I've got a database with 3 tables TAB1, TAB2, TAB3 that have the identical posts, for instance :
TAB1 cola, colb, colc, cold TABB cola, colb, colc, cold ...
Now I must search all distinct "colb" values, this is actually the query :
SELECT DISTINCT colb FROM TAB1
Works perfectly however I'd search all distinct "colb" values during my 3 tables "TAB1", "TAB2", "TAB3" :
SELECT DISTINCT colb FROM TAB1, TAB2, TAB3
And today SQL return me a mistake: "Column 'colb' in area list is ambiguous" After a little search, I understood which was because 'colb' column appear in my 3 tables.
Just how search during my 3 tables a definite value in the same column ? I am unable to make use of the LEFT JOIN because I wan to look during my all 3 tables and never in one of these.
Have you got a concept ? Thanks
select colb from tab1 union select colb from tab2 union select colb from tab3
This single query with union will require proper care of distinct values for you personally.
select colb from tab1 union select colb from tab2 union select colb from tab3;
SELECT DISTINCT TAB1.colb,TAB2.colb,TAB3.colb FROM TAB1, TAB2, TAB3
Choose Distinct cola from tab1 union
Choose Distinct cola from tab2 union
Choose Distinct cola from tab3
In sybase, you should use the syntax that @cherouvim published, imaginable the subquery like a table.
You may even use temp tables
select colb into #t1 from TAB1 insert into #t1(colb) values(select colb from TAB2) insert into #t1(colb) values(select colb from TAB3) select distinct colb from #t1