[SOLVED] Join on empty table

[mo]

I have a query in which I join 3 tables 2 tables have matching data and the third table is currently empty (no rows at all). I want my query to still return results from the successful join even though the 3 table is empty.

 

Tables order_item and store have matching entries and order_fax_status is empty.

SELECT DISTINCT a.`store_id`, a.`store_name`, b.`fax`
FROM `order_item` AS a 
INNER JOIN `store` AS b ON b.`store_id` = a.`store_id`
LEFT JOIN `order_fax_status` AS c ON c.`store_id` = a.`store_id`
WHERE a.`od_id`      = 3
AND   a.`store_id`  <> 1000
AND   c.`fax_status` != 'SUCCESS'

 

If I remove "AND  c.`fax_status` != 'SUCCESS'", the query works.

[fenway]

You can't check a field from a left joined table if it doesn't match, since it will be NULLed out.  Move this to the ON clause:

 

SELECT DISTINCT a.`store_id`, a.`store_name`, b.`fax`
FROM `order_item` AS a
INNER JOIN `store` AS b ON ( b.`store_id` = a.`store_id` )
LEFT JOIN `order_fax_status` AS c ON (c.`store_id` = a.`store_id` AND   c.`fax_status` != 'SUCCESS' )
WHERE a.`od_id`      = 3
AND   a.`store_id`  <> 1000

 

[mo]

You can't check a field from a left joined table if it doesn't match, since it will be NULLed out.  Move this to the ON clause:

 

SELECT DISTINCT a.`store_id`, a.`store_name`, b.`fax`
FROM `order_item` AS a
INNER JOIN `store` AS b ON ( b.`store_id` = a.`store_id` )
LEFT JOIN `order_fax_status` AS c ON (c.`store_id` = a.`store_id` AND   c.`fax_status` != 'SUCCESS' )
WHERE a.`od_id`      = 3
AND   a.`store_id`  <> 1000

 

 

Perfect! Works like intended, thanks.