select distinct and left join

mmy1981 · Jun 16, 2010

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 · Jun 16, 2010

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 · Jun 16, 2010

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

select distinct and left join

mmy1981

Programmer

PHV

MIS

mmy1981

Programmer

Similar threads

Part and Inventory Search

Sponsor