I'm trying to do multiple left joins in SQL Server and I'm getting unexpected results when a value is not found in the table being joined

Question

I am making a SQL query to find the products in a database that are below ROP(reorder point) and that belong to a specific production cell.

To do this I have to do several operations on my selected columns, and I have to join multiple tables.

Here is the SQL that I have written:

Select Statement

SELECT      it.ItemID, descr.ShortDesc, CONVERT(INT,inv.OrderPoint) AS ROP,
                CONVERT(INT, (bin.QtyOnHand - inv.QtyOnSO + inv.QtyOnPO)) AS Avail,
                CONVERT(INT, bin.QtyOnHand) AS QOH, CONVERT(INT,inv.QtyOnSO) AS Sold,
                CONVERT(INT,((bin.QtyOnHand - inv.QtyOnSO)/(sale.PrevYearQtySold/10)*100)) AS percentUsage,
                CONVERT(INT, sale.PrevYearQtySold/10) AS moUsage, CONVERT(INT,inv.MaxStockQty) AS Bin,
                CONVERT(INT, inv.OrderPoint - (bin.QtyOnHand - inv.QtyOnSO)) AS NTS
FROM        timItem AS it

Table Joins

LEFT JOIN   timInventory AS inv
ON  it.ItemKey = inv.ItemKey

LEFT JOIN   timItemClass AS itClass
ON  it.ItemClassKey = itClass.ItemClassKey

LEFT JOIN   timItemDescription AS descr
ON  it.ItemKey = descr.ItemKey

LEFT JOIN   texItemSalesSummary AS sale
ON  it.ItemKey = sale.ItemKey

LEFT JOIN   timWhseBinInvt AS bin
ON  it.ItemKey = bin.ItemKey

Where Clause

WHERE (bin.QtyOnHand - inv.QtyOnSO) < inv.OrderPoint 
AND itClass.ItemClassID = 'A'

ORDER BY

ORDER BY percentUsage ASC

sql with syntax highlighting

Upon the completion of my query I get something that looks like the following:

    Item ID || ShortDesc || ROP || AVAIL || QOH || SOLD || %Usage || mo Use || Bin || NTS

    item       descr        12      -4      47      51       -8         46     24     16
    item       descr        6        0      12      12        0         11     12     6
    item       descr        18       0      44      44        0          9     20     18

This would be fine, minus the fact that if an item in the timWhseBinInvt has a temporary bin location and it's QtyOnHand reaches zero. It's row is automatically deleted from the table (just the way that our MAS500 schema browser describes it).

Thus leaving me with an incomplete result, excluding the items that don't exist in that particular table.

I would like to see data that looks more like this:

    Item ID || ShortDesc || ROP || AVAIL || QOH || SOLD || %Usage || mo Use || Bin || NTS

    item       descr        12      -4      47      51       -8         46     24     16
    item       descr        6       null    null    5         null      11     12     6
    item       descr        18       0      44      44        0          9     20     null

This would give me all of the items in the 'A' production cell, including items that aren't found in the table timWhseBinInvt. The columns corresponding with that table should have a value of null for the items that do not exist within that table.

From my research on different types of joins. it seemed that left joins make this kind of functionality possible.

I've also looked into *is null, not exists,*etc. with no luck.

I apologize if this post is unclear to anybody, I'm very new to writing SQL queries and I thought I'd reach out on here to see if anybody with more experience could help.

Thanks

I would say left joins, but you are already doing that. Maybe if you post the sample data that is used to get to the resultset you posted it would make things more clear — GuidoG, Jan 03 '18 at 16:58
What do you mean by "timWhseBinInvt has a temporary bin location"? — openshac, Jan 03 '18 at 16:59
Using a `LEFT JOIN` to a table and then putting a criteria on a column in that table in your `WHERE` clause implicitly turns the `LEFT JOIN` into an `INNER JOIN`. If you are expecting those to be proper criteria, then use an `INNER JOIN` or put the expressions in your `ON` clauses. — Thom A, Jan 03 '18 at 16:59
Possible duplicate of [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — Tab Alleman, Jan 03 '18 at 17:00

score 1 · Accepted Answer · answered Jan 03 '18 at 17:04

1

Your where clause is turning the outer joins into inner joins. You need to move the conditions to the on clause. I suspect that you want to filter by class A, so this might do what you want:

FROM timItem it JOIN
     timInventory inv
     ON  it.ItemKey = inv.ItemKey JOIN
     timItemClass AS itClass
     ON it.ItemClassKey = itClass.ItemClassKey LEFT JOIN   
     timItemDescription AS descr
     ON it.ItemKey = descr.ItemKey LEFT JOIN
     texItemSalesSummary AS sale
     ON it.ItemKey = sale.ItemKey LEFT JOIN
     timWhseBinInvt AS bin
     ON it.ItemKey = bin.ItemKey AND
        (bin.QtyOnHand - inv.QtyOnSO) < inv.OrderPoint 
WHERE itClass.ItemClassID = 'A'

answered Jan 03 '18 at 17:04

Gordon Linoff

1,242,037
58
646
786

Alternately, you could leave the JOIN conditions the same and change the WHERE clause to `((bin.QtyOnHand - inv.QtyOnSO) < inv.OrderPoint OR bin.QtyOnHand IS NULL)`. It all depends on what you find more readable/maintainable. – Bacon Bits Jan 03 '18 at 17:49
@Gordon Linoff Thanks, moving the condition to the on clause gave me the results I was expecting. gave me quite a few NULL results, but that was to be expected. Thanks! – Jacob Allison Jan 03 '18 at 18:06
@BaconBits I tried using IS NULL on my WHERE clause before posting this actually, however, I was saying it after `bin.QtyOnHand - inv.QtyOnSO) < inv.OrderPoint AND itClass.ItemClassID = 'A'`... writing it like `WHERE ((bin.QtyOnHand - inv.QtyOnSO) < inv.OrderPoint OR bin.QtyOnHand IS NULL) AND itClass.ItemClassID = 'A'` worked like a charm. Thanks! – Jacob Allison Jan 03 '18 at 18:13

score 0 · Answer 2 · answered Jan 03 '18 at 17:03

As stated in my Comment, putting a WHERE clause on a column which is from a table you have performed a LEFT JOIN on implicitly transforms the join to an INNER JOIN. For example the two following statements are the same (even though one uses a LEFT JOIN):

SELECT *
FROM TABLE1 T1
     LEFT JOIN TABLE2 T2 ON T1.ID = T2.ID
WHERE T2.ID = 7;

SELECT *
FROM TABLE1 T1
     JOIN TABLE2 T2 ON T1.ID = T2.ID
WHERE T2.ID = 7;

Instead, put the clause on the "left" table in your ON clause:

SELECT *
FROM TABLE1 T1
     LEFT JOIN TABLE2 T2 ON T1.ID = T2.ID
                        AND T2.ID = 7;

I'm trying to do multiple left joins in SQL Server and I'm getting unexpected results when a value is not found in the table being joined

2 Answers2