CODEWITHSUNDEEP
mysqlsql
Michael
Michael

Reputation: 12826

Select all rows from all tables where column name equals given value

Is there a way to select all the rows across all the tables where the column name matches a certain condition? I know how to select all tables that contain the given column name:

SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'customer_id'

Is there a way to join tables using the table name pulled out? For example, something that works as this (clearly incorrect) query tries to.

SELECT * FROM (SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'customer_id') WHERE customer_id = 1

Upvotes: 2

Views: 19304

Answers (1)

Marc B
Marc B

Reputation: 360842

Not in plain SQL. You can't use the results of one query as a table/field name in an outer query. In your non-working example, you'd get a result set that looks like

+---------------+
| TABLE_NAME    |
+---------------+
| name_of_table |
+---------------+

from which you then try to filter on a customer_id field, which doesn't exist in the inner result.

You can build a query statement dynamically in a stored procedure, and execute that statement as detailed here: How To have Dynamic SQL in MySQL Stored Procedure

However, needing such dynamic queries is usually a sign of bad table design.

Upvotes: 2

Related Questions