Join query help in sqlserver 2000

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)

Join query help in sqlserver 2000

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-28 : 17:00:40

I need the help in getting the output.
if A1 exists in the @prod3 table then the @prod2 table prod2 value for the respective sid should be A1
Please help me in getting the desired output.thanks for yur help in advance..

declare @Prod1 table
(

Csid bigint,
sid bigint
)
insert into @Prod1
select 4103, 101 union all
select 4101, 195 union all
select 4103, 101 Union all
select 4104, 201 Union all
select 4105, 202

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

insert into @Prod2
select 101, 'T1' union all
select 101, 'T2' union all
select 195, 'T5' Union all
Select 201, 'T3' Union all
Select 202, 'T1'

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

insert into @Prod3
select 101, '72' union all
select 195, 'A5' Union all
Select 201, 'A3' Union all
Select 202, 'A1'

Below is the query
--case when max(p3.prod3)='A1' then p2.Prod2='A1'

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'
when max(p2.Prod2) = 'T1' then '5'else max(p2.Prod2) end
,p3.prod3
from @Prod1 p1 inner join @Prod2 p2
on p1.sid = p2.sid
inner join @prod3 p3 on p3.sid=p1.sid
group by p1.Csid, p1.sid,p3.prod3

Output:

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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 17:31:29

I might have misunderstood your question, but won't this do what you want?

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

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-02-28 : 19:41:58

Thanks it works!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources