Rebuild_Stuff is a list of all needed parts. Official_All and Bought_List are "have" lists. Everything in Official_All needs to be doubled.

I want to compare the lists to see what is still needed.

Some parts in Rebuild_Stuff aren't in at least one of the other two lists, so they're returning NULLs, which are messing up the equations. I tried ISNULL and COALESCE, but neither works.

SELECT
    Rebuild_Stuff.BLItemNo,
    Rebuild_Stuff.PartName,
    Rebuild_Stuff.ColorName,
    Rebuild_Stuff.qty AS Need_Qty,
    COALESCE((Official_All.Qty * 2) + Bought_List.Qty_Bought,
    Official_All.Qty,
    Bought_List.Qty_Bought) AS Have_Qty, 
    (Rebuild_Stuff.qty - COALESCE((Official_All.Qty * 2) + Bought_List.Qty_Bought,
    Official_All.Qty,
    Bought_List.Qty_Bought)) AS Still_Needed
FROM
    Rebuild_Stuff
LEFT JOIN 
    Official_All ON Rebuild_Stuff.BLItemNo = Official_All.BLItemNo
                 AND Rebuild_Stuff.PartName = Official_All.PartName
                 AND Rebuild_Stuff.ColorName = Official_All.ColorName
                 AND Rebuild_Stuff.qty = Official_All.qty
JOIN
    Bought_List ON Rebuild_Stuff.BLItemNo = Bought_List.BLItemNo
                AND Rebuild_Stuff.PartName = Bought_List.PartName
                AND Rebuild_Stuff.ColorName = Bought_List.ColorName
                AND Rebuild_Stuff.qty = Bought_List.Qty_Bought
WHERE
    (Rebuild_Stuff.qty - ((Official_All.Qty * 2) + Bought_List.Qty_Bought)) > 0
    OR (Official_All.Qty IS NULL)
    OR Bought_List.Qty_Bought IS NULL

It returns the values when all the tables have the part and the sum of the "have" lists is less than that of the "needed" list. But if the "have" lists don't have the part and Qty is NULL, it doesn't return the values.

A simplified example (that runs in a DBFiddle):

Rebuild_Stuff

Rebuild_Stuff is a list of all needed parts. Official_All and Bought_List are "have" lists. Everything in Official_All needs to be doubled.

I want to compare the lists to see what is still needed.

Some parts in Rebuild_Stuff aren't in at least one of the other two lists, so they're returning NULLs, which are messing up the equations. I tried ISNULL and COALESCE, but neither works.

SELECT
    Rebuild_Stuff.BLItemNo,
    Rebuild_Stuff.PartName,
    Rebuild_Stuff.ColorName,
    Rebuild_Stuff.qty AS Need_Qty,
    COALESCE((Official_All.Qty * 2) + Bought_List.Qty_Bought,
    Official_All.Qty,
    Bought_List.Qty_Bought) AS Have_Qty, 
    (Rebuild_Stuff.qty - COALESCE((Official_All.Qty * 2) + Bought_List.Qty_Bought,
    Official_All.Qty,
    Bought_List.Qty_Bought)) AS Still_Needed
FROM
    Rebuild_Stuff
LEFT JOIN 
    Official_All ON Rebuild_Stuff.BLItemNo = Official_All.BLItemNo
                 AND Rebuild_Stuff.PartName = Official_All.PartName
                 AND Rebuild_Stuff.ColorName = Official_All.ColorName
                 AND Rebuild_Stuff.qty = Official_All.qty
JOIN
    Bought_List ON Rebuild_Stuff.BLItemNo = Bought_List.BLItemNo
                AND Rebuild_Stuff.PartName = Bought_List.PartName
                AND Rebuild_Stuff.ColorName = Bought_List.ColorName
                AND Rebuild_Stuff.qty = Bought_List.Qty_Bought
WHERE
    (Rebuild_Stuff.qty - ((Official_All.Qty * 2) + Bought_List.Qty_Bought)) > 0
    OR (Official_All.Qty IS NULL)
    OR Bought_List.Qty_Bought IS NULL

It returns the values when all the tables have the part and the sum of the "have" lists is less than that of the "needed" list. But if the "have" lists don't have the part and Qty is NULL, it doesn't return the values.

A simplified example (that runs in a DBFiddle):

Rebuild_Stuff

Official_All (all Qtys need to be doubled)

Table Bought_List

Should return:

Instead it returns:

• sql
• sql-server
• null
• left-join

• 2 After one quick look, you almost certainly do not want qty to be part of the JOIN criteria. – Tim Roberts Commented Feb 2 at 0:03
• 2 Tips for asking a good Structured Query Language (SQL) question – Ken White Commented Feb 2 at 0:12
• 1 You still haven't provided the necessary sample data and desired results. (And your example isn't minimal) – Dale K Commented Feb 2 at 0:25
• 1 Shouldn't we need 3 Windows not 2, to make up 5 in the Rebuild_Stuff table? And 2 Seats not 1? Where does doubling come into it, what does that mean exactly? – Charlieface Commented Feb 2 at 1:56
• 1 Thank you Dale for explaining my edit's rejection. After having studied the case, understood the need, created an example (before Katie did; I was still editing my SQL when I saw the "This question was closed" appear), understood the errors, created a solution, then understood why Katie put so much cases in her example (I did the same, to test every possible path), I was really eager to make it reopen, in order to post a more elaborate answer than a comment "here is the fiddle with the solution", and particularly give the 4 bullets that, I think, can help Katie create more streamlined SQL. – Guillaume Outters Commented Feb 2 at 8:01

3 Answers 3

It is much easier and cleaner if you calculate the Have_Qty in a CTE or derived query

SELECT BLItemNo, PartName, ColorName, SUM(Qty) AS Qty
FROM
(
  SELECT BLItemNo, PartName, ColorName, Qty * 2 AS Qty 
  FROM   Official_All
  UNION ALL
  SELECT BLItemNo, PartName, ColorName, Qty_Bought
  FROM   Bought_List  
) h
GROUP BY BLItemNo, PartName, ColorName

With that, just LEFT JOIN from Rebuild_Stuff (Need Qty) to the Have Qty query

SELECT
      r.BLItemNo,
      r.PartName,
      r.ColorName,
      r.qty as Need_Qty,
      COALESCE(h.Qty, 0, 0) as Have_Qty, 
      r.qty - COALESCE(h.Qty, 0) as Still_Needed
FROM Rebuild_Stuff AS r
     LEFT JOIN
     (   -- Have Qty query
         SELECT BLItemNo, PartName, ColorName, SUM(Qty) AS Qty
         FROM
         (
             SELECT BLItemNo, PartName, ColorName, Qty * 2 AS Qty 
             FROM   Official_All
             UNION ALL
             SELECT BLItemNo, PartName, ColorName, Qty_Bought
             FROM   Bought_List  
         ) AS h
         GROUP BY BLItemNo, PartName, ColorName
     ) AS h   ON  r.BLItemNo  = h.BLItemNo
              AND r.PartName  = h.PartName
              AND r.ColorName = h.ColorName
WHERE r.qty - COALESCE(h.Qty, 0) > 0

Also, you should consider using a table alias instead of full table name to make the query neater

By creating a working example, I could find some corrections to your query:

• most importantly: the Left Join should apply to Bought_List too
  Having a Left before Official_All is not sufficient, you have to declare a Left before each table that could miss entries
• Do not condition the Join to AND Rebuild_Stuff.qty = Bought_List.Qty_Bought (idem with Rebuild_Stuff.qty)
  Adding this condition means "if I need 4 wheels, I will ignore that I have 2 wheels bought and 1 set of 2 wheels in my official warehouse, I'll wait until I have either 4 wheels bought or 2 sets of 2 on the shelves"
• coalesce individual qtys instead of coalescing sums
  OK, coalesce(a + b, a, b) works with 2 values, but try to handle 3 sources now... All in all it overcomplicates your SQL (it makes hard to understand what you meant)
• avoid OR xxx.qty IS NULL
  when a simple coalesce more intuitively expresses that no entry means 0 item

Which gives:

Select
    Rebuild_Stuff.BLItemNo,
    Rebuild_Stuff.PartName,
    Rebuild_Stuff.ColorName,
    Rebuild_Stuff.qty as Need_Qty,
    coalesce(Official_All.Qty *2, 0) + coalesce(Bought_List.Qty_Bought, 0) as Have_Qty, 
    Rebuild_Stuff.qty - (coalesce(Official_All.Qty *2, 0) + coalesce(Bought_List.Qty_Bought, 0)) as Still_Needed
From Rebuild_Stuff
Left Join Official_All
ON Rebuild_Stuff.BLItemNo = Official_All.BLItemNo
AND Rebuild_Stuff.PartName = Official_All.PartName
AND Rebuild_Stuff.ColorName = Official_All.ColorName
Left Join Bought_List
ON Rebuild_Stuff.BLItemNo = Bought_List.BLItemNo
AND Rebuild_Stuff.PartName = Bought_List.PartName
AND Rebuild_Stuff.ColorName = Bought_List.ColorName
Where (Rebuild_Stuff.qty -((coalesce(Official_All.Qty, 0) *2) + coalesce(Bought_List.Qty_Bought, 0))) > 0;

One option is to create a cte that will make consolidated have list from the two have list tables and Left Join that cte to your need list table - do the math and filter as you wish:

WITH 
 havs AS 
   ( Select  h.BLItemNo, h.PartName, h.ColorName, Sum(h.Qty) as HaveQty 
     From  ( Select BLItemNo, PartName, ColorName, (2 * Qty) as Qty From Official_All Union All
             Select BLItemNo, PartName, ColorName, Bought_Qty as Qty From Bought_List ) h
     Group By  h.BLItemNo, h.PartName, h.ColorName
   )
Select    needs.BLItemNo, needs.PartName, needs.ColorName, 
          needs.Qty as NeedQTY, Coalesce(havs.HaveQty, 0) as HaveQty, 
          needs.Qty - Coalesce(havs.HaveQty, 0) as Still_Needed
From      Rebuild_Stuff needs
Left Join havs ON(havs.BLItemNo = needs.BLItemNo)
--Where     needs.Qty - Coalesce(havs.HaveQty, 0) > 0;  -- activate to filter out rows with Still_Needed <= 0

R e s u l t :

fiddle