Help! Join question

Hi, All
I have two tables as below, TABLE1 and TABLE2.
TABLE 1: Base
ID PName PPrice
--
1 A 30
2 B 20
TABLE 2: History
ID Ldate Amount
--
1 2005/8/7 50
The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
it will return the result as below:
Ldate PName Amount
--
2005/8/7 A 50
2005/8/7 B null
and when I pass the date of 2005/8/8, it will return the result as below:
Ldate PName Amount
--
2005/8/8 A null
2005/8/8 B nullHere you go..
CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
CREATE TABLE #History(id int, Ldate datetime, amount int)
INSERT INTO #Base VALUES(1, 'A',30)
INSERT INTO #Base VALUES(2, 'B',20)
INSERT INTO #History VALUES(1,'20050807',50)
DECLARE @.DateParam datetime
SET @.DateParam = '20050807'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
SET @.DateParam = '20050808'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Cool! Thomas. That is what i need.
Many Thanks
"Roji. P. Thomas" wrote:

> Here you go..
>
> CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
> CREATE TABLE #History(id int, Ldate datetime, amount int)
> INSERT INTO #Base VALUES(1, 'A',30)
> INSERT INTO #Base VALUES(2, 'B',20)
> INSERT INTO #History VALUES(1,'20050807',50)
> DECLARE @.DateParam datetime
> SET @.DateParam = '20050807'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
> SET @.DateParam = '20050808'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "OKLover" <OKLover@.discussions.microsoft.com> wrote in message
> news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
>
>|||Hi
CREATE TABLE #t1
(
rowid int not null primary key,
pname char(1) not null,
pprice decimal(5,2)
)
insert into #t1 values (1,'A',20)
insert into #t1 values (2,'B',30)
CREATE TABLE #t2
(
rowid int ,
ldate datetime not null,
amn decimal(5,2)
)
insert into #t2 values (1,'20050807',20)
select coalesce(Ldate,'20050808'), PName,sum(pprice+amn)
from #t1 left join #t2
on #t2.rowid=#t1.rowid
and #t2.ldate='20050808'
group by Ldate, PName
Note: you will have to change a coded date value to the parameter.
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Hi
Probably you can try this
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
where B.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"OKLover" wrote:

> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign ke
y.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Your solution will not work because you put the joining condition in the
where clause.
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
> Hi
> Probably you can try this
> declare
> @.compDate datetime
> set @.compDate = '20050807'
> select ISNULL(Ldate,@.compDate), PName, Amount
> from Base B
> full join History H on H.ID=B.ID
> where B.ldate=@.compDate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "OKLover" wrote:
>|||sorry! thank you for the correction
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
and H.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roji. P. Thomas" wrote:

> Your solution will not work because you put the joining condition in the
> where clause.
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
>
>