LEFT OUTER JOIN with WHERE clause on 2nd table, not effecting 1st table

user_10711772 0
Viewed 974

I'm trying to return a set of records from two tables. I'm using LEFT OUTER JOIN, because I want all data from table 1. I only want the data from table 2 JOINED if a certain clause is met.

The clause seems to be overriding the LEFT OUTER JOIN and not returning records from table 1, if a record from table 2 doesn't meet the WHERE clause.

SELECT p.code, p.name, d.product_code, d.detail_type, d.description
  FROM products p
  LEFT OUTER JOIN product_details d
    ON p.code = d.product_code 
 WHERE product_details.detail_type = 'ALTERNATIVE NAMES'

I want all rows from products returned, but I only want rows from product_details to be joined if product_details.detail_type = 'ALTERNATIVE NAMES'

I'm looking for some guidance as to whether this is possible or if I should be stripping out the unwanted data after the initial JOIN?

left-join where-clause mysql sql
2 Answers

use product_details.detail_type = 'ALTERNATIVE NAMES' condition in ON Clause instead of where Clause

SELECT products.code, products.name, product_details.product_code, product_details.detail_type, product_details.description
FROM products 
LEFT OUTER JOIN product_details 
ON products.code = product_details.product_code 
and product_details.detail_type = 'ALTERNATIVE NAMES'
user_69148640

Just replace WHERE with AND to be able to use exactly as LEFT [OUTER] JOIN.

e.g. 

ON products.code = product_details.product_code
        AND product_details.detail_type = 'ALTERNATIVE NAMES'

Otherwise the query treats as an INNER JOIN.

user_58413060
Related