How to get common field in ten tables with different field name

Question

Hi all,

I am having a common field in ten tables with different field name.

example:

table1:

t1_id     t1_location

1         india

2         china

3         america

table2:

t2_id     t2_location

4         london

5         australia

6         america

Now my o/p should be:

location

india

china

america

london

australia

How should i get that using mysql query.

thanks in advance

Answer 1

You can query the locations using UNION, which will also remove duplicates:

Select t1_location As location
From table_1
Union
Select t2_location As location
From table_2

Sounds like it would be a better approach to store all locations in a separate table with ID, and refer to that IDs in your tables.