join multiple tables having some disjoint tables

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 2005 Forums

Transact-SQL (2005)

join multiple tables having some disjoint tables

Author

Topic

msc_timor
Starting Member

11 Posts

Posted - 2008-01-19 : 03:25:19

hi,
can anyone help on this
here's the situation
i have tables as
1. items - contains itemid, description, price etc..
2. shops - contains locationid, shopname, etc..
3. sales - locationid, itemid, sale_qty
4. purchase - locationid, itemid, purchase_qty

i want shop wise item stock quantity
for individual shop i can query out but it dosent work for all shops
at one go

any suggestions

waitin'............................
mipham

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-19 : 03:44:41

try if this gives you desired result?

SELECT s.shopname,i.description,SUM(sl.saleqty)-SUM(p.purchaseqty) AS Stock
FROM shops s
INNER JOIN sales sl
ON sl.locationid=s.locationid
INNER JOIN purchase p
ON p.locationid=s.locationid
AND p.itemid=sl.itemid
INNER JOIN items i
ON i.itemid=sl.itemid
GROUP BY s.shopname,i.description

msc_timor
Starting Member

11 Posts

Posted - 2008-01-30 : 00:17:01

hello,
i tried this and also the outer joins, but dosent works
if i try the inner join,
the only data common to both are output
as the case is that the item in sales may not be in the purchase and vica-versa
and if i try the outer join
the combination of all the items with locations is displayed which is also not a relevent data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 00:40:58

Can you specify your exact requirement with some sample data & expected o/p then?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources