How can I make such a selection

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

General SQL Server Forums

New to SQL Server Programming

How can I make such a selection

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2009-11-10 : 07:00:38

I have two tables
1)LocationMaster with fields locationId,locationName
2)LocationProductQuantity with fields locationId,productName,Quantity

How can I make a select command such that I will result location quantity for each product (0 if no entry in LocationProcuctQuantity for a location)
ie if LocationMaster contains
locationId locationName
1 Loc1
2 Loc2
3 Loc3
and LocationProductQuantity contains
locationId productName Quanity
1 pen 10
2 pen 20
2 book 5
3 book 40

It should show as

Loc1 pen 10
Loc2 pen 20
Loc3 pen 0
Loc1 book 0
Loc2 book 5
Loc3 book 40

Thanks and Regards
Anu Palavila

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 07:19:44

select t1.locationid,t2.productname,isnull(t2.quantity,0) from LocationMaster as t1 left join LocationProductQuantity as t2 on t1.locationid=t2.locationid

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources