CODEWITHSUNDEEP
mysql
Marox
Marox

Reputation: 359

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.

Upvotes: 1

Views: 2482

Answers (2)

Jerms
Jerms

Reputation: 11

Try the following:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c1
JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.TABLE_NAME=c2.TABLE_NAME
JOIN INFORMATION_SCHEMA.COLUMNS c3 ON c2.TABLE_NAME=c3.TABLE_NAME 
                                  AND c1.TABLE_NAME=c3.TABLE_NAME
WHERE c1.COLUMN_NAME = 'gps time'
  AND c2.COLUMN_NAME = 'shape'
  AND c3.COLUMN_NAME = 'id'

Upvotes: 1

Shaharyar
Shaharyar

Reputation: 12449

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

Upvotes: 2

Related Questions