asked on

SQL query for reporting from 2 tables

I am trying to run a query that will return results from 2 tables, the first table is the master table for my inventory, the second table contains orders. I need to run a report that shows everything from my master table and using the orders table qty column to show how many items have been ordered. So for example I have 500 items in my master table and there were 230 items from the orders table, I want to show all 500 items and how many of each were ordered, if the item in the master table does not have an order then it should show 0 or null.

The query I am using is only returning just the items that were ordered.

SELECT Pricing_Master.price_id, Pricing_Master.partNumber, Pricing_Master.shortDescription, Pricing_Master.longDescription, Pricing_Master.pricing_01, SUM(TI_DEF_END_ITEMS.qty) AS ordered
FROM Pricing_Master INNER JOIN
TI_DEF_END_ITEMS ON Pricing_Master.price_id = TI_DEF_END_ITEMS.price_id
GROUP BY Pricing_Master.price_id, Pricing_Master.partNumber, Pricing_Master.shortDescription, Pricing_Master.longDescription, Pricing_Master.pricing_01

appari

try

SELECT Pricing_Master.price_id, Pricing_Master.partNumber, Pricing_Master.shortDescription, Pricing_Master.longDescription, Pricing_Master.pricing_01, SUM(TI_DEF_END_ITEMS.qty) AS ordered
FROM Pricing_Master left outer JOIN
TI_DEF_END_ITEMS ON Pricing_Master.price_id = TI_DEF_END_ITEMS.price_id
GROUP BY Pricing_Master.price_id, Pricing_Master.partNumber, Pricing_Master.shortDescription, Pricing_Master.longDescription, Pricing_Master.pricing_01

ASKER CERTIFIED SOLUTION

appari

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Devil666

hi there,

try the following

SELECT Pricing_Master.price_id, Pricing_Master.partNumber, Pricing_Master.shortDescription, Pricing_Master.longDescription, Pricing_Master.pricing_01, SUM(ISNULL(TI_DEF_END_ITEMS.qty, 0)) AS ordered
FROM Pricing_Master LEFT OUTER JOIN
TI_DEF_END_ITEMS ON Pricing_Master.price_id = TI_DEF_END_ITEMS.price_id
GROUP BY Pricing_Master.price_id, Pricing_Master.partNumber, Pricing_Master.shortDescription, Pricing_Master.longDescription, Pricing_Master.pricing_01

hth

digital_slavery

ASKER

Ok I feel stupid! using the left outer join was the key to getting this to work.

Thanks so much