select distinct and left join
select distinct and left join
(OP)
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
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
Talk To Other Members
RE: select distinct and left join
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: select distinct and left join
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