Page 1 of 1

[SOLVED] using DISTINCT and abiguous fields

Posted: Tue Feb 14, 2006 3:08 am
by duk
hy guys,

im trying to do a simple and faster query, but my problem is that i can select my ID column...

i have try use IN, JOIN and DISTINCT and the DISTINCT is the faster with 0.00 sec, IN takes to me 1.22sec and JOIN is something like 0.22sec but dont give me the results i wnat...

my poblem is that using DISTINCT im not able to select ID from my workers table, say that is abiguous...

here the 3 querys
mysql> select distinct id,surname,name from workers,workers_positions where work
ers.id=id_worker;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
id i take the id and left just surname and name will work fine...
mysql> select id,name from workers where id IN (select id_worker from workers_po
sitions );
and this one takes 1.22sec
mysql> select name,surname from workers join workers_positions where workers.id=
id_worker;
and this one just dont help me because takes the 12results and displays the same name 12times... its just to show if im doing something wrong with this one...

i need some help with distinct because i need to use my ID field... some idea ??

thanks in advance

Posted: Tue Feb 14, 2006 3:19 am
by feyd
you have two tables with the same field name(s). This is why you get "ambiguous" errors. In this case, both have the field 'id'.

Code: Select all

SELECT DISTINCT `workers`.`id`, `surname`, `name` FROM `workers`, `workers_positions` WHERE `workers`.`id` = `id_worker`

Posted: Tue Feb 14, 2006 3:31 am
by duk
realy thanks feyd

can you give me a hand to use this with DISTINCT


mysql> select id,name from workers where id IN (select id_worker from workers_po
sitions where id_position='1');

Posted: Tue Feb 14, 2006 3:37 am
by duk
solved :D
mysql> select distinct workers.id,workers_positions.id_position,name,surname fro
m workers,workers_positions where workers.id=id_worker AND workers_positions.id_
position=1;
and incredible tanks less 1.22secs then IN...[/quote]