list the tables only if all the listed columns exists - MySQL

Question

I have tried the below query to list the tables that contains the listed 4 columns. But it returns no tables.

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'moduleid'
        AND COLUMN_NAME = 'userroleid'
        AND COLUMN_NAME = 'sortorder'
        AND COLUMN_NAME = 'setdefault'
        AND TABLE_SCHEMA='db_name'

when i use only one column in where condition it returns the tables. I need to know the tables that has all 4 columns.

Answer 1

This might not be the most elegant solution but it should work.

SELECT DISTINCT c1.TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS c1 
JOIN INFORMATION_SCHEMA.COLUMNS c2 on c1.TABLE_NAME = c2.TABLE_NAME
JOIN INFORMATION_SCHEMA.COLUMNS c3 on c1.TABLE_NAME = c3.TABLE_NAME
JOIN INFORMATION_SCHEMA.COLUMNS c4 on c1.TABLE_NAME = c4.TABLE_NAME
WHERE c1.COLUMN_NAME = 'moduleid'
    AND c2.COLUMN_NAME = 'userroleid'
    AND c3.COLUMN_NAME = 'sortorder'
    AND c4.COLUMN_NAME = 'setdefault'
    AND c1.TABLE_SCHEMA='db_name'