HI,

I have a database with 3 tables TAB1, TAB2, TAB3 which have exactly the same columns, for example :

TAB1
cola, colb, colc, cold
TABB
cola, colb, colc, cold
...

Now I would like to search all distinct "colb" values, this is the query :

SELECT DISTINCT colb FROM TAB1

Works perfectly but now I would search all distinct "colb" values in my 3 tables "TAB1", "TAB2", "TAB3" :

SELECT DISTINCT colb FROM TAB1, TAB2, TAB3

And now SQL return me an error: "Column 'colb' in field list is ambiguous" After some search, I understood that was because 'colb' column exist in my 3 tables.

So how search in my 3 tables a distinct value from the same column ? I cannot use the LEFT JOIN because I wan to search in my all 3 tables and not in one of them.

Do you have an idea ? Thanks

Comments

Thank's everyone !!!

Written by Jensen

Accepted Answer

This single query with union will take care of distinct values for you.

select colb from tab1 union
  select colb from tab2 union
  select colb from tab3;
Written by cherouvim
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki