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...
>
>

HELP!

hi!
I have a table who name is table0. its have a values like colum0: 01 colum1
: d01 and other table(this name is table1) have linked values to 01 and
d01. I want to update d01 value in table1, look from table0. I have
32000-35000 value in table0 and table1.So how can I do it?
Thanks,,,,,,
<m_guner18@.hotmail.com> wrote in message news:...
> hi!
> I have a table who name is table0. its have a values like colum0: 01
colum1
> : d01 and other table(this name is table1) have linked values to 01 and
> d01. I want to update d01 value in table1, look from table0. I have
> 32000-35000 value in table0 and table1.So how can I do it?
> Thanks,,,,,,
>
|||update table0
set colum0= 01, colum1= d01
from table0,table1
where table1.column0=table0.column0 and table1.column1=table0.column1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"m_guner18@.hotmail.com" wrote:

> hi!
> I have a table who name is table0. its have a values like colum0: 01 colum1
> : d01 and other table(this name is table1) have linked values to 01 and
> d01. I want to update d01 value in table1, look from table0. I have
> 32000-35000 value in table0 and table1.So how can I do it?
> Thanks,,,,,,
>
>

Help With Update Please

I have a table called table1 that looks like this:
CName, Calls,Lost
--,--,--
Test, 18
Test1, 3
Test2, 4
Test5, 23
Test6, 21
Test7, 8
Test8, 4
Test9, 2
Test10, 23
I need to set Lost to 1 (True) ONLY where Calls is the minimum. So in this
case Lost would be equal to 1 for record Test9 ecause Calls equals 2 (the
minimum). HOWEVER, if another Calls equaled 2 ( the lowest number) then I
wouls not set Lost equal to 1 for any row.
Basically I need to set Lost equal to 1 only where Calls is the lowest
without a tie.
Any thoughts?
thank you.One solution would be to...
Update Table
Set Lost = 1
Where (Calls = (Select min(Calls) From Table))
Depending on how you want this to work if there were two records with Calls
= 2.
"Bill" <msnews.microsoft.com> wrote in message
news:eM2SiFtvFHA.3864@.TK2MSFTNGP12.phx.gbl...
>I have a table called table1 that looks like this:
> CName, Calls,Lost
> --,--,--
> Test, 18
> Test1, 3
> Test2, 4
> Test5, 23
> Test6, 21
> Test7, 8
> Test8, 4
> Test9, 2
> Test10, 23
> I need to set Lost to 1 (True) ONLY where Calls is the minimum. So in
> this case Lost would be equal to 1 for record Test9 ecause Calls equals 2
> (the minimum). HOWEVER, if another Calls equaled 2 ( the lowest number)
> then I wouls not set Lost equal to 1 for any row.
> Basically I need to set Lost equal to 1 only where Calls is the lowest
> without a tie.
> Any thoughts?
> thank you.
>
>|||Thanks. However, I only want to update the row where no other roh has the
lowest Calls.
"Marshall" <marshall@.newsgroup.nospam> wrote in message
news:uV%23NxKtvFHA.1996@.TK2MSFTNGP10.phx.gbl...
> One solution would be to...
> Update Table
> Set Lost = 1
> Where (Calls = (Select min(Calls) From Table))
> Depending on how you want this to work if there were two records with
> Calls = 2.
> "Bill" <msnews.microsoft.com> wrote in message
> news:eM2SiFtvFHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||So If I understand correctly, If you had the dataset of:
CName, Calls,Lost
--,--,--
Test, 18
Test1, 3
Test2, 4
Test5, 23
Test6, 21
Test7, 8
Test8, 4
Test9, 2
Test10, 23
Test11, 2
Where Test9 and Test11 both have calls=2. In this case which row would get
updated?
"Bill" <msnews.microsoft.com> wrote in message
news:uMacKOtvFHA.2960@.tk2msftngp13.phx.gbl...
> Thanks. However, I only want to update the row where no other roh has the
> lowest Calls.
>
> "Marshall" <marshall@.newsgroup.nospam> wrote in message
> news:uV%23NxKtvFHA.1996@.TK2MSFTNGP10.phx.gbl...
>|||As I understand it neither of the rows would be updated.|||Yes. Lost should equal 1 ONLY where the row has the lowest calls but is not
tied.
Eg.
test1,8,0
test2,5,0
test9,5,0
but...
test1,8,0
test2,5,1
test9,6,0
Calls has to be tthe very lowest without a tie in order for lost to be 1
"Marshall" <marshall@.newsgroup.nospam> wrote in message
news:%235pq4btvFHA.908@.tk2msftngp13.phx.gbl...
> So If I understand correctly, If you had the dataset of:
> CName, Calls,Lost
> --,--,--
> Test, 18
> Test1, 3
> Test2, 4
> Test5, 23
> Test6, 21
> Test7, 8
> Test8, 4
> Test9, 2
> Test10, 23
> Test11, 2
> Where Test9 and Test11 both have calls=2. In this case which row would
> get updated?
> "Bill" <msnews.microsoft.com> wrote in message
> news:uMacKOtvFHA.2960@.tk2msftngp13.phx.gbl...
>|||Actually, in the following...
CName, Calls,Lost
--,--,--
Test, 18
Test1, 3
Test2, 4
Test5, 23
Test6, 21
Test7, 8
Test8, 4
Test9, 2
Test10, 23
Test9 row would be 1 because 2 is the lowest calls and there are no other
rows with 2 as the lowest calls.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127326106.801682.12070@.z14g2000cwz.googlegroups.com...
> As I understand it neither of the rows would be updated.
>|||What about this? It's not pretty but I think it works how you want it
too.
If (Select count(*) From t
Where Calls = (Select Min(Calls) From t)) = 1
Begin
Update t
Set Lost = Null
Update t
Set Lost = 1
Where Calls = (Select Min(Calls) From T)
End
Else
Begin
Update t
Set Lost = Null
End
HTH
Barry|||Think of it a different way...
Team,Score,WinLoseOrTie
team1,18,nowin
team2,23,nowin
team3,10,win
team4,50,nowin
team,5,23,nowin
BUT...
Team,Score,WinLoseOrTie
team1,18,nowin
team2,23,nowin
team3,10,nowin
team4,50,nowin
team,5,10,nowin
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127326106.801682.12070@.z14g2000cwz.googlegroups.com...
> As I understand it neither of the rows would be updated.
>|||Did this work?