How to construct complex SELECT statement

Question

I want to make complex SELECT statement but I'm not sure how to do it

I can write it human speaking language so

I want to select all product_id and its corresponding brand_name

Tables are these

1) Brands (links brand id to brand name) Contains lines with brand_id and corresponding brand_name

2) Field_brand (links entity to brand id. entity is a product in shop, each entity belongs to one brand. product can contain one or more product variants (see next table)) Contains lines with entity_id and corresponding brand_id

3) Field_products (links product with its variants, i.e. several product_id's can have one entity_id) Contains lines with product_id and corresponding entity_id

I mean, product_id is the name of variant, entity_id is the name of product — Lambrusco.Newbie
Sorry, you ask us to read the documentation for you, since you don't feel like? — arkascha
I read lot of documentation but I am somewhat confused when to use what joins, I tried to write some queries but they overloaded the server, seems they were wrong — Lambrusco.Newbie
This question appears to be off-topic because it demonstrates no research effort — Strawberry

DrCopyPaste DrCopyPaste · Accepted Answer · 2014-01-21T08:29:44

This should get you on the right track

SELECT
        Field_products.product_id,
        Brands.brand_name
    FROM Brands
    INNER JOIN Field_brand on Brands.brand_id = Field_brand.brand.id
    INNER JOIN Field_products on Field_brand.entity_id = Field_products.entity_id

To understand joins better: you do an inner join if you want to have the result set reduced to the set that is shared between all joined sets (i.e. you only want rows that can be matched between your joined sets) , that is what is happening here.

If you were to use left joins for example, the result set would include every entry from the original set plus the corresponding ones, but no non-corresponding row gets removed (no rows from the original set would be left out if they could not be matched to the joined set).

How to construct complex SELECT statement

1 Answers