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
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;
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki