Issue with Join

Question

I have two tables in a master detail layout. I want to select all Active items from the Master Table and detail records only if they meet a condition. But I want all Master table items no matter id a detail record exists or not.

My tables look like: Master - inventoryItem

icmasterid  icdescription
WAD110795       WM KIWI-KLIP 3 BEND                    
WAD110796       WM KIWI-KLIP 3 BEND (MZ3,4,&6)                    
WAD118806-50    STROLLER VALENCE BACKDRP HINGE                    
WAD118808       IK STROL B DROP MOUNTING HDW    
WAD118942       1" S-HOOK BAG 100

Detail - inventoryLine

icmasterid  icdetailquantity    pomasterid
WAD110796               -900    NULL
WAD110796                  0    NULL
WAD110796                  0    119450
WAD110796                900    119347
WAD118808                  0    NULL
WAD118808                 34    NULL
WAD118942                  0    NULL
WAD118942                 59    NULL
WAD118942                  0    NULL
WAD118942                -59    NULL
WAD118942                 59    NULL

my sql

SELECT inventoryitem.icmasterid,
       inventoryitem.icdescription,
       inventoryline.icdetailquantity,inventoryline.pomasterid
FROM  inventoryitem 
      LEFT OUTER JOIN inventoryline ON inventoryitem.icmasterid=inventoryline.icmasterid 
WHERE inventoryitem.icmasterid < 'WAD18' and inventoryitem.icmasterid like 'WAD%' 
ORDER BY inventoryitem.icmasterid

The above gives me all items in inventoryitem and their related inventoryline records as expected.

But when I add

and inventoryline.pomasterid <> ''

I get just two rows.

icmasterid  ICdescription                      icdetailquantity   pomasterid
WAD110796   WM KIWI-KLIP 3 BEND (MZ3,4,&6)                  0   119450
WAD110796   WM KIWI-KLIP 3 BEND (MZ3,4,&6)                900   119347

What I need is

icmasterid     icdescription                      icdetailquantity  pomasterid
WAD110796      WM KIWI-KLIP 3 BEND (MZ3,4,&6)                    0  119450
WAD110796      WM KIWI-KLIP 3 BEND (MZ3,4,&6)                  900  119347
WAD118806-50   STROLLER VALENCE BACKDRP HINGE                  NULL NULL
WAD118808      IK STROL B DROP MOUNTING HDW                    NULL NULL
WAD118942      1" S-HOOK BAG 100                               NULL NULL

Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jun 27 '19 at 07:48
This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. For SQL that includes DDL & tabular-formatted input initialization. — philipxy, Jun 27 '19 at 07:51
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jun 27 '19 at 07:51

Tim Biegeleisen · Answer 1 · 2019-06-26T23:48:56.870

2

Move the logic in the WHERE clause to the ON clause:

SELECT
    ii.icmasterid,
    ii.icdescription,
    ii.icdetailquantity,inventoryline.pomasterid
FROM inventoryitem ii
LEFT JOIN inventoryline il
     ON ii.icmasterid = il.icmasterid AND
        -- SUBSTRING(ii.icmasterid, 4, 2) < '18' AND
        ii.icmasterid LIKE 'WAD%' 
ORDER BY
    ii.icmasterid;

edited Jun 26 '19 at 23:48

answered Jun 26 '19 at 23:09

Tim Biegeleisen

502,043
27
286
360

That was not quite the answer but pointed me to the answer. ``` SELECT ii.icmasterid, ii.icdescription, ii.icdetailquantity,inventoryline.pomasterid FROM inventoryitem ii LEFT JOIN inventoryline il ON ii.icmasterid = il.icmasterid AND inventoryline.pomasterid<> '' WHERE ii.icmasterid < 'WAD18' AND ii.icmasterid LIKE 'WAD%' ORDER BY ii.icmasterid; ``` sorry I could not figure out how to format this. – farley Jun 26 '19 at 23:24
Actually I think you need to update your logic which checks the `icmasterid` column. – Tim Biegeleisen Jun 26 '19 at 23:41
No, it is correct. It is there because there are 10,000 plus rows in the table. Checking for < 'WAD18' is only there to give me testable sample. Your Substring would give me a of not wanted records. Thanks for your help. – farley Jun 26 '19 at 23:45

score 0 · Accepted Answer · answered Jun 26 '19 at 23:50

Based on Tim Biegeleisen's answer I came up with this.

SELECT
    ii.icmasterid,
    ii.icdescription,
    ii.icdetailquantity,inventoryline.pomasterid
FROM inventoryitem ii
LEFT JOIN inventoryline il
    ON ii.icmasterid = il.icmasterid AND and inventoryline.pomasterid <> ''
WHERE
    ii.icmasterid < 'WAD18' AND
    ii.icmasterid LIKE 'WAD%' 
ORDER BY
    ii.icmasterid;

Issue with Join

2 Answers2