CODEWITHSUNDEEP
mysql
Greg
Greg

Reputation: 426

SQL Return search set and result

How can an sql query to be written so that it will return whether an id is found, for each id searched? Every id searched for in the IN statement should have a row, specifying if a record with that id was found in the table.

Query guess

Select [id] From table1 Where [id] IN (1, 2, 3, 4, 5)

Goal Result Set

ID   Found
1    yea
2    naw
3    yea
4    yea
5    naw

Upvotes: 0

Views: 44

Answers (2)

Greg
Greg

Reputation: 426

Here is an approach that uses a temp table rather than IN operator or several unions.

Create temp table

Declare Global Temporary Table session.temp (id decimal(15, 0))

Populate temp table

Insert Into session.temp (id) values ((1),(2),(3),(4),(5))

Query

SELECT session.temp.id,
       case when count(table1.id) > 0 Then 'Yep' Else 'Nope' End As Found
FROM session.temp
LEFT JOIN table1
ON session.temp.id = table1.id
GROUP BY temp.id

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This version doesn't use IN operator, rather it uses union in order to get table expression:

create table main(id int);
insert into main
select 1 union
select 3 union
select 5


select t.id,
       case when count(m.id) > 0 then 'yep' else 'nope' end as found

from 
(select 1 as id union
 select 2 as id union
 select 3 as id union
 select 4 as id union
 select 5 as id
)t

left join main m on t.id = m.id
group by t.id

Output:

id  found
1   yep
2   nope
3   yep
4   nope
5   yep

Upvotes: 1

Related Questions