Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query joins help in sqlserver 2000

Status
Not open for further replies.

jrprogr

Programmer
Jul 20, 2006
74
US
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
 
I am getting the desired output with the below query..
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
Is it the correct...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top