HI,

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