select distinct and left join

[mmy1981]

Hi,

I have 2 tables, for example:
table1 with columns: id - agentid
with values:
1 - agent1
2 - agent2
3 - agent3

and table2 with columns id - info
with values:
1 - info 1
1 - info 2
1 - info x
2 - info y

Is it possible to display all the values from tabel1 and the info from tabel2 (but without the duplicate id's?)

example:
id - agent - info with values:
1 - agent1 - info 1 (or info2, or info3)
2 - agent2 - info y
3 - agent3 - /

I tried the left join query...
SELECT table1.id, table1.agentid, table2.info
FROM table1 LEFT JOIN table2 ON
table1.id = table2.id

but as expected, this shows id 1 3 time (with info1, info2 and info3). I tried SELECT distinct(table1.id), ... but this doesn't work.

Any help is appreciated

 

[PHV]

A starting point:
SELECT table1.id, table1.agentid, MIN(table2.info)
FROM table1 LEFT JOIN table2 ON table1.id = table2.id
GROUP BY table1.id, table1.agentid

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmy1981]

PHV, thanks for the reply.

I used the following
1) create a query with DISTINCT with the unique ID's
2) create a query and display the ID's from the first query (the unique ID's) and the info from the other tables (agentid + info). Using a left join...

I'll check the MIN() function. Thanks