LEFT JOIN有多行结果

Question

I have 3 tables products,colors,assign with this structure:

products:

product_id | default_color
1          | 1

colors

color_id | color_name
1        | Black
2        | Green
3        | Yellow

assign

product_id | color_id
1          | 1
1          | 2
1          | 3 

My query returns only first row from "assign" table, I need to have ALL colors assigned for current product.

Here is a query I use:

SELECT * FROM products p
LEFT JOIN assign a ON p.product_id = '1' 
LEFT JOIN colors c ON a.color_id = c.color_id
WHERE p.product_id = 1

Answer 1

Try this:

SELECT * FROM products p
LEFT JOIN assign a ON a.product_id = p.product_id
LEFT JOIN colors c ON c.color_id = a.color_id
WHERE p.product_id = 1

The join condition should be made on column names, not on values.
For filtering results, use where.

See here a working sqlFiddle.

For more info on join, see the official docs here.