CODEWITHSUNDEEP
mysqlleft-join
enano2054
enano2054

Reputation: 329

SQL query with LEFT JOIN returning no results

I have 2 MySQL tables IM_Transfer and IM_INV. I run the following query regularly:

SELECT
    `IM_Transfer`.`sku`,
    `IM_Transfer`.`vendor_sku`,
    `IM_Transfer`.`Description`,
    `IM_Transfer`.`Receiver`,
    `IM_INV`.`LOC_ID`, 
    `IM_INV`.`QTY_ON_HND` 
FROM 
    `IM_Transfer` 
LEFT JOIN 
    `IM_INV` 
ON 
    `IM_Transfer`.`sku` = `IM_INV`.`ITEM_NO` 
WHERE `LOC_ID` = "AF" 
    OR `LOC_ID` = "LO" 
    OR `LOC_ID` = "S" 
    OR `LOC_ID` = "SL" 
ORDER BY 
    `IM_Transfer`.`sku`, 
    `IM_INV`.`LOC_ID` 
    ASC

Most of the time, most of the lines in IM_Transfer match by IM_Transfer.sku and IM_INV.ITEM_NO and the ones that don't come back with null values in the last 2 columns.

I just ran this query with a a single row in IM_Transfer and it does not have any matching data in IM_INV. How do I update the query to still return a row with just null in the LOC_ID and QTY_ON_HND columns?

Thanks in advance

Upvotes: 3

Views: 3268

Answers (1)

Steven Ensslen
Steven Ensslen

Reputation: 1396

The where clause only evaluates to true when there is a row in the outer joined table. Referencing a column from the outer joined table in a where clause usually makes the join into an inner join.

I think that the query that you want is:

SELECT
  `IM_Transfer`.`sku`,
  `IM_Transfer`.`vendor_sku`,
  `IM_Transfer`.`Description`,
  `IM_Transfer`.`Receiver`,
  `IM_INV`.`LOC_ID`, 
  `IM_INV`.`QTY_ON_HND` 
FROM 
  `IM_Transfer` 
LEFT JOIN 
  `IM_INV` 
ON 
`IM_Transfer`.`sku` = `IM_INV`.`ITEM_NO` 
 AND ( `LOC_ID` = "AF" 
  OR `LOC_ID` = "LO" 
  OR `LOC_ID` = "S" 
  OR `LOC_ID` = "SL" ) 
ORDER BY 
  `IM_Transfer`.`sku`, 
  `IM_INV`.`LOC_ID` 
ASC

Notes:

  1. ASC is the default sort order, so it doesn't need to be specified.
  2. Your LOC_ID filter is simpler when expressed as LOC_ID IN ("AF","LO","S","SL")

Thanks @SeanLange for pointing me in the right direction.

Upvotes: 1

Related Questions