Search code examples
mysqlaggregate-functions

Mysql - finding tables with missing columns

I have various schema that contain a table called c_alertaction. In some schema, this table contains a column called 'creator', but in others, this column is missing.

I can fairly easily see the state of play with a left-join (all instances have an 'oid' column):

select c1.table_schema, c1.table_name, c1.column_name, 
c2.table_schema, c2.table_name, c2.column_name
from information_schema.columns as c1
left join information_schema.columns as c2 on
    c1.table_schema = c2.table_schema
    and c1.table_name = c2.table_name
    and c2.column_name = 'creator'
where c1.table_name = 'c_alertaction'
and c1.column_name = 'oid';

However, I feel convinced that I can do this without a left join, using aggregate functions, but so far, I'm stumped.

Here's my attempt:

select c.table_schema, count(*) as CreatorCount
from information_schema.columns as c
where c.table_name = 'c_alertaction'
and c.column_name = 'creator'
group by c.table_schema;

But that will only report schemas that have the column.

Any suggestions/clues/ideas?

Solution

  • See example

    select c.table_schema
  ,sum(case when c.column_name = 'creator' then 1 else 0 end) as CreatorCount
from information_schema.columns as c
where c.table_name = 'c_alertaction'
group by c.table_schema;