Khona Khona - 1 month ago 11
MySQL Question

Returns multiple table id's in a single query to see whether they exists or not?

I am given some primary key values from multiple tables. I want to check whether they exists or not in a single query. I have tried with this query.

select BR.id, AREA.id, HOUSE.id, TABLET.id, REGION.id, AC.id, SUPERVISOR.id
from BR, AREA, HOUSE, TABLET, REGION, AC, SUPERVISOR
where BR.id in (851) or AREA.id in (91) or HOUSE.id in (566) or TABLET.id in (896, 897, 898) or
REGION.id in (89) or AC.id in (775) or SUPERVISOR.id in (895)
GROUP BY BR.id, AREA.id, HOUSE.id, TABLET.id, REGION.id, AC.id, SUPERVISOR.id


But it returns a huge number of results and quiet inefficient. Is there any way to check existence of multiple ids of multiple tables in a single query that run efficiently?

Answer

Use a UNION statement

select 'BR' as table_name, id from BR where id in (851)
union all
select 'AREA' as table_name, id from AREA where id in (91)
union all
select 'HOUSE' as table_name, id from HOUSE where id in (566)
union all
select 'TABLET' as table_name, id from TABLET where id in (896, 897, 898)
union all
select 'REGION' as table_name, id from REGION where id in (89)
union all
select 'AC' as table_name, id from AC where id in (775) 
union all
select 'SUPERVISOR' as table_name, id from SUPERVISOR where id in (895)

The result would be something like

| table_name | id  |
|------------|-----|
| BR         | 851 |
| HOUSE      | 566 |
| TABLET     | 896 |
| TABLET     | 897 |
Comments