SQL Question

[wetech]

I have a table that looks like this:

ID Type Value
1 1 10
1 2 15
2 2 20
3 1 25
3 2 30


Not all ID's have Type =1, but they all have Type = 2. What I need to do is return the Value for an ID where Type = 1 if Type 1 exists. If there is no Type 1, then return the Value for Type = 2. I need only one value per ID (ie. Don't return the value for Type = 1 and Type =2). I'm having trouble returning just the Type 1 value, and not also returning the Type 2 value for an ID. Any help is appreciated.

 

[Crusty]

If you're using MYSQL it would be:

SELECT IFNULL((SELECT Value FROM table WHERE Type = 1 AND ID = <input> ),(SELECT Value FROM table WHERE Type = 2 AND ID = <input> ));

At least that's how I would do it 🙂

 

[wetech]

Thanks.

 

[Whitedog]

Outer joins are more efficient.

select isnull(a.value, b.value) value
from table b
left outer join table a
on a.id = b.id
where a.type = 1 and b.type = 2