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
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.
Instead of p.product_id = '1' use a.product_id
SELECT * FROM products p
LEFT JOIN assign a ON p.product_id = a.product_id
LEFT JOIN colors c ON a.color_id = c.color_id
WHERE p.product_id = 1`
Your SQL statement seems to list all three rows from assign table.
I executed your query:
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
And I am getting the three rows of assign table.
Here is my SQLFiddle: http://sqlfiddle.com/#!9/50f8bd/1/0
Try this query. Everything is the same as yours except I'm using GROUP_CONCAT() function to concatinate ALL colors for a given product into one field.
SELECT
p.product_id,
GROUP_CONCAT(c.color_name ORDER BY c.color_name ASC SEPARATOR ', ') AS colors
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
RESULT:
product_id | colors
-----------|-------------
1 | Black, Green, Yellow
DEMO: