Joining 3 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Joining 3 tables

Author

Topic

medotnet
Starting Member

14 Posts

Posted - 2007-09-08 : 07:03:05

Hello,

I have 2 main tables Sales(PNo, Qty) and Purchase(PNo,Qty),
and I need to get the total qty from them i.e.

Select PNo, P.PNo-S.PNo as TotalQty
From Purchase P, Sales S

the problem as you see is that PNo remains ambiguous and can't be aliased to any of the 2 tables becuase a product can be bought but not yet sold or even sold without being bought.

I tried to link those 2 tables to a master table but didn't work either.

So what do you think?
thanks in advance,
Medo

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-08 : 09:24:25

Maybe this:

SELECT
CASE
WHEN purchase.QTY is null
THEN sales.pno
ELSE purchase.pno
END AS PNO,
ISNULL(SUM(purchase.QTY-sales.QTY),0) AS Inventory
FROM Purchase
FULL JOIN SALES
ON purchase.pno = sales.pno
GROUP BY purchase.pno,
sales.pno ,
purchase.qty

Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources