CODEWITHSUNDEEP
sqlsql-server
user366312
user366312

Reputation: 17008

How to include those tuples that don't fulfill a specific criterion?

Items(ItemCatalogNo, ItemType, ManufacturerName, Model, Price, QtyInWarehouse)

Supplies(SupplyNo, SupplierID, DateOrdered, DateReceived, ItemCatalogNo, Amount)

For each item type and manufacturer, give the total number of items of that were supplied.

SELECT  Items.ItemCatalogNo,
        Items.ItemType, 
        Items.ManufacturerName,
        sum(Supplies.Amount) as TotalSupply
FROM    Items, 
        Supplies    
WHERE   Items.ItemcatalogNo = Supplies.ItemcatalogNo
GROUP BY Items.ItemType, Items.ItemCatalogNo,Items.ManufacturerName
;

This query only gives the tuples of those ItemTypes which were supplied.

If I also need to incorporate those items that were not supplied (in which case, TotalSupply should show 0 or null), what to do?

Should I use UNION?

Upvotes: 1

Views: 21

Answers (2)

Roberto
Roberto

Reputation: 2185

left outer join retrieves a row even when the condition is not met, filling the secondary table (supplies) with nulls.

coalesce(first, second) replaces null values in first parameter with second parameter, so when amount is null, it sums 0. Actually you can have multiple parameters, coalesce takes the left-most value that is not-null. 

SELECT  Items.ItemCatalogNo,
        Items.ItemType, 
        Items.ManufacturerName,
        sum(coalesce(Supplies.Amount,0)) as TotalSupply
FROM    Items 
left outer join Supplies on Items.ItemcatalogNo = Supplies.ItemcatalogNo
GROUP BY Items.ItemType, Items.ItemCatalogNo, Items.ManufacturerName

Note: When using more than one table in a query prefer using left outer join (or join) to link them through its primary and foreign key, and let the where clause for actual conditions that retrieved rows must comply. This way is easier to distinguish the domain relationships from the particular conditions in the query.

Upvotes: 2

automatic
automatic

Reputation: 2737

Use a left join

SELECT  Items.ItemCatalogNo,
    Items.ItemType, 
    Items.ManufacturerName,
    sum(Supplies.Amount) as TotalSupply
FROM    Items 
Left outer join Supplies    
on   Items.ItemcatalogNo = Supplies.ItemcatalogNo
GROUP BY Items.ItemType, Items.ItemCatalogNo,Items.ManufacturerName;

If you want zero instead of null for non-existing items use coalesce.

SELECT  Items.ItemCatalogNo,
  Items.ItemType, 
  Items.ManufacturerName,
  coalesce(sum(Supplies.Amount),0) as TotalSupply
FROM    Items 
Left outer join Supplies    
on   Items.ItemcatalogNo = Supplies.ItemcatalogNo
GROUP BY Items.ItemType, Items.ItemCatalogNo,Items.ManufacturerName;

Upvotes: 2

Related Questions