Advanced SQL query with lots of joins

Posted by lund.mikkel on Stack Overflow See other posts from Stack Overflow or by lund.mikkel
Published on 2010-06-09T11:39:11Z Indexed on 2010/06/09 11:42 UTC
Read the original article Hit count: 300

Filed under:
|
|
|

Hey fellow programmers

Okay, first let me say that this is a hard one. I know the presentation may be a little long. But I how you'll bare with me and help me through anyway :D

So I'm developing on an advanced search for bicycles. I've got a lot of tables I need to join to find all, let's say, red and brown bikes. One bike may come in more then one color! I've made this query for 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 the many joins. The Products table mainly holds product id and it's image and the Products Description table holds more descriptive info such as name (and product ID of course).

I then have the Products Options Values table which holds all the colors and their IDs. Products Options Search is containing the color IDs along with a color group ID (products_options_search_id). Red has the color group code 4 (brown is 5).

The products and colors have a many-to-many relationship managed inside Products Attributes.

So my question is first of all: Is it okay to make so many joins? Is i hurting the performance?

Second: If a bike comes in both red and brown, it'll show up twice even though I use SELECT DISTINCT. Think this is because of the INNER JOIN. Is this possible to avoid and do I have to remove the doubles in my PHP code?

Third: Bikes can be double colored (i.e. black and blue). This means that there are two rows for that bike. One where it says the color is black and one where is says its blue. (See second question). But if I replace the OR in the WHERE clause it removes both rows, because none of them fulfill the conditions - only the product. What is the workaround for that?

I really hope you will and can help me. I'm a little desperate right now :D

Regards Mikkel Lund

© Stack Overflow or respective owner

Related posts about sql

Related posts about search