Query joins help in sqlserver 2000

[jrprogr]

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

 

[jrprogr]

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