CODEWITHSUNDEEP
mysqlsqldatabase
Hugo
Hugo

Reputation: 767

Which MYSQL Join to include not existing records

I have a database that I want to query for all records in products joined with product_fields. But it is possible there is no record in product_fields for the color, in that case I still want to have a record shown, only field color can be left empty. The product_fields table consist of several records for the same product and has a 'name' and 'value' field.

See this statement I came up with:

SELECT p.*, c.value AS color
FROM products p
LEFT JOIN product_fields c ON c.product_id = products.id 
WHERE c.name = 'color'
AND products.id = '1'

To my surprise this does not work, I must have left something obvious out? Thanks!

Upvotes: 1

Views: 491

Answers (1)

John Woo
John Woo

Reputation: 263893

You need to put the condition in the ON clause,

SELECT p.*, c.value AS color
FROM   products p
       LEFT JOIN product_fields c 
            ON c.product_id = products.id AND 
               c.name = 'color'
WHERE  products.id = '1' 
LIMIT  1

The main difference between adding a condition in the ON clause and on the WHERE clause is that, in ON clause, it filters out the records from the specified table before joining it on the other table while WHERE clause filters out records after the table has been joined.

Upvotes: 2

Related Questions