If/Else in select - 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 2005 Forums

Transact-SQL (2005)

If/Else in select

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-23 : 15:41:27

Not sure if I should use if/else or case/when or how to use it

I have this table
ID Type Value
1 1 14
1 2 15
2 1 27
2 2 25
3 1 33

I want to select only those with Type =2, if some ID does not have that type, then select with type 1. So my result table would be

ID Type Value
1 2 15
2 2 25
3 1 33

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-11-23 : 15:54:51

NO need to use if or case, you can do it with simple select statment with where calaus

select * from table1
where type = 2
or
type = 1

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-23 : 15:58:51

Thanks.

But when an ID has both type 1 and 2, it will select both. I only want to select type 2. If type 2 does not exists, then select type 1.

Maybe we can use IF EXISTS ?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-23 : 16:03:24

Do a UNION

Select * from table1 where type = 2
UNION
Select * from table where [ID] not in (Select [ID] From table1 where type = 2])

which is much like the if exists option I suppose

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-23 : 16:44:55

Dataguru,

You got the right idea. Got it to work. Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-23 : 16:59:54

Here's another way to do it, which may perform better

SELECT T2.ID, T2.Type, T2.Value
FROM yourtable T1
LEFT JOIN yourtable T2 ON T1.ID = T2.ID AND T1.Type = T2.Type
WHERE T2.Type = 2 OR (T2.Type = 1 AND T1.Type IS NULL)

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-23 : 17:43:37

Thanks, do you mean left join is faster than UNION ?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-23 : 18:46:25

Yes, the join method would be faster and more efficient if you compare them an QA...should have suggested that one as well.

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-23 : 19:17:04

Dataguru idea is more clearer to me to implement. Here is what I change to get it to work
Select * from @T1 where C =2 and T in (1,2,3,5,7,10)
UNION
Select * from @T1 where T not in (Select T From @T1 where C = 2 and T in (1,2,3,5,7,10))
order by E,T

Now i'm trying snSQL idea...but looking at this the whole day, my head spins...and it didn't get what i want... ;)

SELECT T2.E, T2.C, T2.T,T2.Q
FROM @T1 T1
LEFT JOIN @T1 T2 ON T1.E = T2.E AND T1.C = T2.C
WHERE T2.C = 2 OR (T2.C = 1 AND T1.C IS NULL)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-23 : 19:32:14

With that many variables it may be easier to stick with what works...

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources