MySql select all tables with multiple specific columns

Question

I want to get all table names which has 3 specific columns.

What I want is from information schema I want to get all table names which contains columnA AND columnB AND columnC.

Currently I am using a query like

SELECT DISTINCT TABLE_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME='columnA'
        AND TABLE_SCHEMA='mysampledatabase';

How can I extend the above query and select tables which contains column names columnA and columnB ?

---

I came across this answer and other similar answers. They have answered following:

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';

However what they select any table which has either of the 3 columns. I believe that is because of WHERE COLUMN_NAME IN ('columnA','ColumnB','ColumnC') which , i believe, is equivalent to COLUMN_NAME = 'columnA' OR COLUMN_NAME = 'columnB' OR COLUMN_NAME = 'columnC'

---

I have tried to replace the WHERE clause with WHERE COLUMN_NAME='columnA' AND COLUMN_NAME='columnB' AND COLUMN_NAME='columnC' which obviously doesn't return any results because COLUMN_NAME can only be 1 value at a time!

Answer 1

This is a faster version:

SELECT TABLE_NAME 
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE COLUMN_NAME IN ('columnA','columnB','columnC')
group by TABLE_NAME
having count(distinct COLUMN_NAME) = 3 

In above query having count will have no. of columns specified in IN phrase.

or try following(but it will create disaster for so many columns. So avoid it)

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN 
( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'columnA' )
and TABLE_NAME in 
( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'columnB')
and TABLE_NAME in 
( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'columnC')