Advanced SQL query with many joins

Question

OK, first let me say this is complicated. I know that a presentation can be a little long. But I, as you are with me, and help me in any case: D

So, I am developing an advanced bike search. I have many tables that I need to join to find all, say, red and brown bicycles. One bike can have more than one color! I made this request now:

SELECT DISTINCT p.products_id,                    #simple product id
                products_name,                    #product name
                products_attributes_id,           #color id
                pov.products_options_values_name  #color name
FROM   products p
       LEFT JOIN products_description pd
         ON p.products_id = pd.products_id
       INNER JOIN products_attributes pa
         ON pa.products_id = p.products_id
       LEFT JOIN products_options_values pov
         ON pov.products_options_values_id = pa.options_values_id
       LEFT JOIN products_options_search pos
         ON pov.products_options_values_id = pos.products_options_values_id
WHERE  pos.products_options_search_id = 4         #code for red
       OR pos.products_options_search_id = 5      #code for brown

My first concern is a lot of associations. The table Productsbasically contains the product identifier, and the image and table Products Descriptioncontain more descriptive information such as the name (and product identifier, of course).

Products Options Values, . Products Options Search (products_options_search_id). 4 ( - 5).

"--", Products Attributes.

, : ? ?

: , , SELECT DISTINCT. , - INNER JOIN. , PHP-?

-: (.. ). , . , , , - . (. ). OR WHERE, , - . ?

, . : D

Answer 1

, -, SQL . , , .

, , :

SELECT DISTINCT p.products_id,                    #simple product id
                products_name,                    #product name
                products_attributes_id,           #color id
                pov.products_options_values_name  #color name
FROM   products p
       LEFT JOIN products_description pd
         ON p.products_id = pd.products_id
WHERE p.products_id in (
       Select products_id from products_attributes pa #This will give you the ID forall bikes that have either red or brown in them
       INNER JOIN products_options_values pov
         ON pov.products_options_values_id = pa.options_values_id
       INNER JOIN products_options_search pos
         ON pov.products_options_values_id = pos.products_options_values_id
       WHERE  pos.products_options_search_id = 4         #code for red
            OR pos.products_options_search_id = 5      #code for brown)

, , . , , .

, PHP- ( , ), - ( , , , ).

, , , , . , , , , , .

Answer 2

. , .

distinct select. , , , , . , , (, , , - ). ( , , , ). : , .

. or ? and no, , ( ).

Answer 3

. ON , , .

, , SO , : https://data.stackexchange.com/stackoverflow/query/2695/so3005416-comparison-select-questions-with-any-selected-tags

, , . Select.

Answer 4

, . ( ) ( ) , .

Assuming you can't do anything with table structures, an alternative query might be:

SELECT p.products_id,                          #simple product id
       products_name,                          #product name
       min(products_attributes_id),            #lowest color ID
       max(products_attributes_id),            #highest color ID
       min(pov.products_options_values_name),  #lowest color name
       max(pov.products_options_values_name)   #highest color name
FROM   products p
       LEFT JOIN products_description pd
         ON p.products_id = pd.products_id
       INNER JOIN products_attributes pa
         ON pa.products_id = p.products_id
       INNER JOIN products_options_values pov
         ON pov.products_options_values_id = pa.options_values_id
       INNER JOIN products_options_search pos
         ON (pov.products_options_values_id = pos.products_options_values_id AND
        pos.products_options_search_id IN (4, 5) )         #codes for red, brown
group by p.products_id, products_name

Depending on which dialect of SQL (SQLServer, Oracle, MySQL, etc.) you are using, it is possible that the syntax for the final condition may be slightly different.

Obviously, when only one color is returned, the lowest and highest values ​​will be the same.