Imran Azam Imran Azam - 4 months ago 7
SQL Question

SQL query for finding all records with Table Name from multiple table

I have three table Table1,Table2,Table3. Each table contains Column "Comments". So i want to find the records with table name.

For Example:

Table1

Id Comments

98 test

99 test

100 attach


Table2

Id Comments

101 test

102 test

103 module


Table3

Id Comments

111 test

112 test

113 exist


If i say
select * from Table1,Table2,Table3 where comments like '%test%'


Result should be like this :

Id Table Comments

98 Table1 test

99 Table1 test

101 Table2 test

102 Table2 test

111 Table3 test

112 Table3 test

Answer

You could use a UNION query:

SELECT Id, 'Table1' AS Table, Comments
FROM Table1
WHERE Comments LIKE '%test%'
UNION ALL
SELECT Id, 'Table2' AS Table, Comments
FROM Table2
WHERE Comments LIKE '%test%'
UNION ALL
SELECT Id, 'Table3' AS Table, Comments
FROM Table3
WHERE Comments LIKE '%test%`