CODEWITHSUNDEEP
sqlsybase
stu
stu

Reputation: 8805

Can I use the exists function in the select part of an SQL query?

I need to run a query where one of the fields returned is a yes or no if there is a row in another table matching one of the key fields in the first table. Sounds like a job for join, except the second table is one to many and I just need to know if there are zero or a non zero number of rows in the secondary table.

I could do something like this:

select t1.name, t1.id, (select count(1) from t2 where t1.id=t2.id) from t1

but I'd like to avoid making an aggregate subquery if possible. It was mentioned to me that I could use the exists() function, but I'm not seeing how to do that in a select field.

This is sybase 15 by the way.

Upvotes: 0

Views: 5965

Answers (4)

LHA
LHA

Reputation: 9655

How about this query ( Work with all databases )

select t1.name, t1.id, 'Y' as HasChild
from t1 
where exists ( select 1 from t2 where t2.id = t1.id)
UNION
select t1.name, t1.id, 'N' as HasChild
from t1 
where NOT exists ( select 1 from t2 where t2.id = t1.id)

Upvotes: 0

Virus
Virus

Reputation: 3425

I am just writing down the syntax here:

if exists (select * from table1 t1 inner join table1 t2 on t1.id = t2.id )
  select * from table2

Upvotes: 0

Lamak
Lamak

Reputation: 70658

You could still do the JOIN, something like this:

SELECT t1.name, t1.id, CASE WHEN t2.id IS NULL THEN 0 ELSE 1 END Existst2
FROM t1 
LEFT JOIN (SELECT id FROM t2 GROUP BY id) t2
ON t1.id = t2.id

Upvotes: 1

stu
stu

Reputation: 8805

ahhh, I got it from another stackoverflow quetion...

case when exists (select * from t2  where t1.id = t2.id) then 1 else 0  end

Upvotes: 0

Related Questions