Multi table search

[fredsnertz]

I have 3 tables (A,B,C) and each has an "id" column. I'm trying to create a query that will tell me if any of the three tables has a row where id is a specific value. I could do this as 3 separate queries but I feel like I should be able to do it as one.

 

[PHV]

Something like this ?

SELECT * FROM (
SELECT 'A' TableName, ID FROM TableA
UNION SELECT 'B', ID FROM TableB
UNION SELECT 'C', ID FROM TableC
) U
WHERE id='specific value'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fredsnertz]

I am a little confused. In the example 'A', 'B', and 'C' are my table names? What are 'TableA', 'TableB', and 'TableC'? Thanks for the reply.

 

[r937]

in PHV's example, 'A', 'B', and 'C' are values of a 1-byte "flag" column which identify the table that the rows in the UNION come from

since you are only interested in whether the particular id value is present, and not which table it came from, you can omit those

SELECT * 
  FROM (
       SELECT ID FROM TableA
       UNION 
       SELECT ID FROM TableB
       UNION 
       SELECT ID FROM TableC
       ) U
 WHERE id = [i]specificvalue[/i]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon