using multiple tables in case in sql2005

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 2005 Forums

Transact-SQL (2005)

using multiple tables in case in sql2005

Author

Topic

san79
Starting Member

42 Posts

Posted - 2009-12-17 : 02:26:50

hi , i am having two tables codemaster and selectedcodes both of them having codeid as common field. i want to display a table which shows all the codes from the codemaster as well as a custom field as status , the status will show y for those codes which is present in selectedcodes table and n for the rest i tried using searched case statement but it is returning the multiplication of rows of two tables, my code is

select mstcodification.codeid , 'status' =
case
when mstcodeparts.codeid=mstcodification.codeid then 'y'
else 'n'
end
from mstcodification,mstcodeparts

i tried to use where and join but the i am not getting expected result, any help will be greatly appreciated. Thanks

Sachin.Nand

2937 Posts

Posted - 2009-12-17 : 04:44:08

Could u post some sample data so that things get a bit clear.

PBUH

san79
Starting Member

42 Posts

Posted - 2009-12-17 : 05:41:24

@Idera
sample data
table mstcodeparts
ccd code part
1 QSPPG0038017036 2
1 QTMGR0000030055 2

table mstcodification
codeid type range
QSPPG0038017036 SPPG 38.17/38.364
QTMGR0000030055 BLLI 0.2
ADAV TWST 0-60
ADAVI TPGG 0-70

the output i am aiming is
status codeid
-1 QSPPG0038017036
-1 QTMGR0000030055
0 ADAV
0 ADAVI

hope i am clearer now
although i have found a way to achieve this. i am using subquery and isnull to do that , but i am not sure about its performance i think it is ugly .
the query i used is

select isnull(
(select '-1' from mstcodeparts b where b.codeid=a.codeid and b.partid='2' and b.co_code='1'),'0') as [status] ,a.codeid from mstcodification a

Sachin.Nand

2937 Posts

Posted - 2009-12-17 : 07:14:28

Not sure about the performance but your query will be better then mine performance wise


create table #mstcodeparts (co_code int,codeid varchar(50),partid int)
insert into #mstcodeparts
select 1, 'QSPPG0038017036', 2 union all
select 1 ,'QTMGR0000030055', 2


create table #mstcodification (codeid   varchar(50),type  varchar(50),range varchar(50))
insert into #mstcodification
select  'QSPPG0038017036','SPPG', '38.17/38.364' union all
select 'QTMGR0000030055 ','BLLI', '0.2' union all
select 'ADAV','TWST' ,'0-60' union all
select  'ADAVI','TPGG', '0-70' 




select * from #mstcodeparts
select * from #mstcodification
--san79

select isnull( 
(select '-1' from #mstcodeparts b where b.codeid=a.codeid and b.partid='2' and b.co_code='1'),'0') as [status] ,
a.codeid from #mstcodification a

--Idera
select case when nullif(a.partid,null)IS null then 0 else -1 end as status,
 b.codeid from #mstcodeparts a
full outer join #mstcodification  b on a.codeid=b.codeid

drop table #mstcodeparts
drop table #mstcodification

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources