Three Table Join With Subquery --Please Help

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)

Three Table Join With Subquery --Please Help

Author

Topic

vineet.tanwar
Starting Member

5 Posts

Posted - 2012-02-16 : 04:09:00

I have 3 Tables

Table 1 Item Detail
ItemId
Color
Description

Table 2 Bill
BillId
ItemId
Price

Table 3 DailyStock
ItemID
Date
StockInHand

Now i want to join these three tables with ItemId with conditions

1. All Data from Table 1
2. Price From table 2 where billid is max for the itemid
3. StockInHand from table 3 where date is max for the itemid

i am able to join the tables but not able to put the conditions on the join.

Thank you

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-02-16 : 04:29:21

select T1.itemID, T1.color,T1.Description , T2.price
from table1 as T1
outer apply
(
select top 1 price
from table2
where table2.itemID=T1.itemID
order by billID desc ) T2

select T1.itemID, T1.color,T1.Description , T3.stocInHand
from table1 as T1
outer apply
(
select top 1 stocInHand
from table3
where table3.itemID=T1.itemID
order by date desc ) T3

vineet.tanwar
Starting Member

5 Posts

Posted - 2012-02-16 : 04:37:05

quote:
Originally posted by stepson

select T1.itemID, T1.color,T1.Description , T2.price
from table1 as T1
outer apply
(
select top 1 price
from table2
where table2.itemID=T1.itemID
order by billID desc ) T2

select T1.itemID, T1.color,T1.Description , T3.stocInHand
from table1 as T1
outer apply
(
select top 1 stocInHand
from table3
where table3.itemID=T1.itemID
order by date desc ) T3

Thank you. But this query is giving me two tables can we put all the results in a single table.

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-02-16 : 05:14:20

select T1.itemID, T1.color,T1.Description , T2.price,T3.stocInHand
from table1 as T1
outer apply
(
select top 1 price
from table2
where table2.itemID=T1.itemID
order by billID desc ) T2
outer apply
(
select top 1 stocInHand
from table3
where table3.itemID=T1.itemID
order by date desc ) T3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources