Find equal columns in different tables in mysql

Question

There are three tables, each one having approximately 60 columns and I have to find all the common columns between them.

Is there a way to find automatically the common columns between three different tables?

Why do you need it, as the better way would be to check existence of a value in all three tables rather than getting all the values from the tables and then search for common results.. — Niket Malik, Aug 24 '13 at 06:15

score 1 · Accepted Answer · answered Aug 24 '13 at 06:41

1

select distinct COLUMN_NAME, count(TABLE_NAME) as COMMON_COUNT
from information_schema.COLUMNS
where TABLE_NAME in ('table1', 'table2', 'table3') and TABLE_SCHEMA = 'dbname'
group by TABLE_SCHEMA, COLUMN_NAME
having COMMON_COUNT > 1

answered Aug 24 '13 at 06:41

cHao

84,970
20
145
172

That was exactly what I needed. It was even very intuitive. Thank you. – Rafael Vidal Aug 26 '13 at 19:13

score 0 · Answer 2 · answered Aug 24 '13 at 06:21

I don't know how your table looks, so I would just write how I would do it (P.S there can be better ways)

1) select all the values from the first table.

2)make two loops, first to search from second table, and second to search from third table.

3) count number of rows.

4) if number of rows is more than one, you know you have duplicate.

Find equal columns in different tables in mysql

2 Answers2