3 table join - 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 2008 Forums

Transact-SQL (2008)

3 table join

Author

Topic

shantanu88d
Starting Member

35 Posts

Posted - 2011-05-12 : 02:20:16

hi,
I have these tables

telephone_master:
model_brand || model_number || purchase_qty || available_qty

telephone_alottment:
sr_no || model_brand || model_number || qty

lost_telephones:
sr_no || lost_by || qty

Now i want a Query like this:
model_brand || model_number || purchased_qty || available_qty || lost

in this query lost qty is obtained by looking up sr_no fo lost telephone in telephone_alottment which is stored in lost_telephones table as lost_by. And ofcourse entire query is grouped by model_number and model_brand

Plz plz help, I cant find solution to this !!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-12 : 03:24:20

[code]
select m.model_brand, m.model_number, m.purchased_qty, m.available_qty, lost = isnull(l.qty, 0)
from telephone_master m
left join
(
select a.model_brand, a.model_number, qty = sum(qty)
from telephone_alottment a
inner join lost_telephones l on a.sr_no = l.sr_no
group by a.model_brand, a.model_number
) l on m.model_brand = l.model_brand
and m.model_number = l.model_number
[/code]

KH
[spoiler]Time is always against us[/spoiler]

shantanu88d
Starting Member

35 Posts

Posted - 2011-05-12 : 05:50:42

awesome....thanks, I wrote this query. It works too.


select m.model_brand, m.model_number,m.purchased_qty, m.available_qty, isnull(lost.lost_qty,0) as lost_qty 
from mobile_master m full outer join (select t.model_number, t.model_brand, l.qty as lost_qty
										from telephone_alottment t, lost_telephones l
										where l.lost_by = t.sr_no) as lost
on lost.model_number = m.model_number and lost.model_brand = m.model_brand
order by model_brand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources