Advanced SQL query with lots of joins
- by lund.mikkel
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
