Searching (1 Viewer)

[burton]

Query that searchs multiple tables

Hi i siiting at my computer rakeing my brain trying to think of a way to make a query that will search 4 different tables for a certain name, , can anyone help or is this a hopeless quest, i welcome any feed back. Cheers Paul

 

[FoFa]

You can use a UNION query to search 4 tables

SELECT 'Table1' as WhichTable, Name
From Table1 where Name like '%name%'
UNION ALL
SELECT 'Table2', Name
From Table2 where Name like '%name%'
UNION ALL
SELECT 'Table3', Name
From Table3 where Name like '%name%'
UNION ALL
SELECT 'Table4', Name
From Table4 where Name like '%name%'

Would return the table name it was found in (WhichTable) and the Name