Marox Marox - 2 months ago 11
MySQL Question

Search for multiple column names in every table, and return table names

I'm trying to write a single query, for returning table names for every table in my database, that has 3 column names:

gps time, shape, id
.
I was looking for an answer, but everything works only for one column name.

SELECT table_name
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name'
AND COLUMN_NAME = 'column_name'


When I add another
"AND COLUMN_NAME = 'column_name2'"
it returns 0.

Thank you in advance for any help.

Answer
SELECT table_name 
FROM information_schema.COLUMNS 
WHERE 
TABLE_SCHEMA = 'db_name' 
AND COLUMN_NAME IN ('column_name', 'column_name2', 'column_name3')
GROUP BY table_name
HAVING COUNT(*) = 3