Left Join Display All Data From Table1 and Table2

Question

I am trying to do a left join so that I get all of my rows from Table 1 even if there is no value corresponding to it in the second table.

My structures are:

Location Table:

ID     LocName
1      Trk1
2      Trk2
3      Trk3
4      Unk

Quantity Table:

ID    PartID   Quantity LocationID
1     1        2        1
2     3        12       2
3     2        6        1
4     6        8        3
5     6        5        1

I am trying to join but also make a query on a specific PartID. My query is:

SELECT   
INV_LOCATIONS.ID AS LocationID,  
INV_LOCATIONS.NAME AS LocationName,  
INV_QUANTITY.QUANTITY AS Quantity  
FROM INV_LOCATIONS  
LEFT JOIN INV_QUANTITY ON INV_LOCATIONS.ID = INV_QUANTITY.LOCATION_ID  
WHERE INV_QUANTITY.PART_ID = 1;

My output right now would be:

ID   LocName   Quantity
1    Trk1      5
3    Trk3      8

The Desired output is:

ID   LocName   Quantity
1       Trk1      5
2       Trk2      NULL/0
3       Trk3      8
4       Unk       NULL/0

I assume it is because I have the WHERE INV_QUANTITY.PART_ID = 1 and that is forcing it to be in the quantity table. I need to be able to verify it is on the right part but how do I also include it if it doesn't exist. I know I have done something very similar before but I cannot remember which project and so I cannot find the code anywhere.

Put this condition: `INV_QUANTITY.PART_ID = 1` in the ON clause instead of WHERE — forpas, Apr 28 '20 at 19:16
Does this answer your question? [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, Apr 28 '20 at 20:19
This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 28 '20 at 20:20
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] When you get a result you don't expect, isolate the first erroneous subexpression & explain what you expect & why — philipxy, Apr 28 '20 at 20:23

score 1 · Accepted Answer · answered Apr 28 '20 at 19:42

1

You need to move the filtering logic to the ON clause:

SELECT il.ID AS LocationID, il.NAME AS LocationName,  
       iq.QUANTITY AS Quantity  
FROM INV_LOCATIONS il LEFT JOIN
     INV_QUANTITY iq
     ON il.ID = iq.LOCATION_ID AND iq.PART_ID = 1;

answered Apr 28 '20 at 19:42

Gordon Linoff

1,242,037
58
646
786

Left Join Display All Data From Table1 and Table2

1 Answers1