[SOLVED] using DISTINCT and abiguous fields

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

[SOLVED] using DISTINCT and abiguous fields

Post 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
Last edited by duk on Tue Feb 14, 2006 3:56 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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`
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post 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');
Last edited by duk on Tue Feb 14, 2006 3:44 am, edited 1 time in total.
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post 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]
Post Reply