Multi table search

fredsnertz · May 21, 2009

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 · May 21, 2009

Something like this ?

Code:

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 · May 21, 2009

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 · May 22, 2009

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

Code:

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Multi table search

fredsnertz

Vendor

PHV

MIS

fredsnertz

Vendor

r937

Technical User

Similar threads

Part and Inventory Search

Sponsor