Search code examples
sqlsql-serverdatabaserdbms

SQL Query Compare Columns of different tables even when NULL

I am by no means strong with SQL syntax and am teaching myself as I go. I am struggling with a SQL query that I am using to compare prices (current and past) of inventory items based off of a finished goods parent item.

The inventory items are either purchased or created in-house. I have 3 tables to compare: a table [FinishedGood] that contains the parent item and the components it requires, a table [Inventory] that contains all inventory items (both purchased and created in-house), and a table [Purchased] that holds only purchased items. The latter two hold pricing values.

As an example, let's say I am making a YO-YO. I make the body of the yo-yo but buy the string and shaft. The body will be a static expense found in the 2nd table. However, the purchased goods can fluctuate in price (the 2nd table contains the most recent price for all goods). The 3rd table can be used to see the price in the last 3 months for only purchased goods.

Now the problem I am seeing is when I compare table 3 (only purchased goods prices) my query results hide the in-house created items (I've attached a picture of what I am wanting to achieve) And below is a sample of what I've written so far.

Any help would be greatly appreciated.

SELECT DISTINCT
    vdvFinishedGood.StepID AS [Step],
    vdvFinishedGood.ComponentID AS [Component],
    vdvInventory.LastUnitCost AS [Current Cost],
    (vdvPurchased.Cost) AS [Purchased Cost]
FROM 
    vdvFinishedGood
JOIN 
    vdvInventory ON vdvInventory.ComponentID = vdvFinishedGood.ComponentID
JOIN
    vdvPurchased ON vdvPurchased.ComponentID = vdvFinishedGood.ComponentID
WHERE 
    vdvFinishedGood.ItemId = 'YOYO'

https://i.sstatic.net/bF2MZ.jpg

http://sqlfiddle.com/#!18/8638ad/1/0

Solution

  • You need to use a LEFT JOIN instead of a JOIN when joining the table Purchased. I modified your fiddle as:

    SELECT DISTINCT
    FinishedGood.StepID AS [Step],
    FinishedGood.ComponentID AS [Component],
    Inventory.LastUnitCost AS [Current Cost],
    Purchased.Cost AS [Purchased Cost]

FROM FinishedGood
JOIN Inventory ON Inventory.ComponentID = FinishedGood.ComponentID
LEFT JOIN Purchased ON Purchased.ComponentID = FinishedGood.ComponentID

WHERE FinishedGood.ItemId = 'YOYO'

ORDER BY [Step]

    Result:

     Step  Component  Current Cost  Purchased Cost 
 ----- ---------- ------------- -------------- 
 001   Body       1.00          (null)         
 002   String     0.25          0.55           
 003   Shaft      1.15          1.15

    See modified fiddle at SQL fiddle.