CODEWITHSUNDEEP
mysql
luis prescod
luis prescod

Reputation: 1

Search multiple tables.column for specific value

I've got multiple tables(8) with the same column name. Each table is a site. I want to be able to search to see which table.column has a specific value. ie. table.subnet = '10.3.30.x'. Thanks in advance.

Upvotes: 0

Views: 465

Answers (2)

codeling
codeling

Reputation: 11389

Check out the UNION ALL keyword. I guess what you want to do is something like this:

(SELECT 'table1', table1.* FROM table1 where subnet = '10.3.30.x')
UNION ALL
(SELECT 'table2', table2.* FROM table2 where subnet = '10.3.30.x')
UNION ALL
...
(SELECT 'table7', table7.* FROM table7 where subnet = '10.3.30.x')
UNION ALL
(SELECT 'table8', table8.* FROM table8 where subnet = '10.3.30.x')

That way, your result would have all columns where the subnet value is the specified value, along with the name of the table that row appeared in.

Upvotes: 0

hafichuk
hafichuk

Reputation: 10781

You can use a UNION to get all of the values in a single query:

SELECT 'table1', subnet FROM table1 WHERE subnet = '10.3.30.x'
UNION
SELECT 'table2', subnet FROM table2 WHERE subnet = '10.3.30.x'
UNION
SELECT 'table3', subnet FROM table3 WHERE subnet = '10.3.30.x'
UNION
SELECT 'table4', subnet FROM table4 WHERE subnet = '10.3.30.x';

Upvotes: 2

Related Questions