query help - 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 2000 Forums

SQL Server Development (2000)

query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-26 : 10:24:07

Please help me in getting the ouput:
Rule if the same sid is more than one then it will harded the value 4.if the sid is one then it should display the what ever value it has..

declare @Prod1 table
(

Csid bigint,
sid bigint
)
insert into @Prod1
select 4103, 101 union all
select 4101, 195 union all
select 4103, 101

declare @Prod2 table
(
sid int,
Prod2 char(2)
)

insert into @Prod2
select 101, 'T1' union all
select 101, 'T2' union all
select 195, 'T5'

Output:
Csid sid prod2
-------------------- ----------- -----
4103 101 4
4101 195 T5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 10:31:14

[code]
select p1.Csid, p1.sid,
prod2 = case when count(*) > 1 then '4' else max(p2.Prod2) end
from @Prod1 p1 inner join @Prod2 p2
on p1.sid = p2.sid
group by p1.Csid, p1.sid
[/code]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-26 : 15:52:30

Thanks KH..for your reply..I want the results like below..

I want to update the values of T5 to 2,T3 to 1..

Csid sid prod2
-------------------- -------------------- -----
4103 101 4
4101 195 2
4104 201 1

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-26 : 18:50:42

I got the desired results with the below query...

select p1.Csid, p1.sid,
prod2 = case when count(*) > 1 then '4'
when max(p2.Prod2) = 'T5' then '1'
when max(p2.Prod2) = 'T3' then '2'
else max(p2.Prod2) end
from @Prod1 p1 inner join @Prod2 p2
on p1.sid = p2.sid
group by p1.Csid, p1.sid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources