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!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

left joining the same table = duplicates 1

Status
Not open for further replies.

piti

Technical User
Apr 12, 2001
627
SK
i have a query similar to this
select table1.col, t2_1.name as name1, t2_2.col_name as name2
from
table1
left join table2 t2_1 on table1.id = t2_1.id
left join table2 t2_2 on table1.id = t2_2.id and t2_2.id2 <> t2_1.id2

to retrieve data from table2 based on data in table1, there must not be related data in table2, but there can be max 2 records in table2 related to table1, but if there are 2 records i receive a result set with 2 rows with the same information, just the values selected from table2 are switched
e.g.
col | name1 | name2
1 test test2
1 test2 test

i want to prevent this kind of duplicates, any idea how?
 
select table1.col
, t2_1.name as name1
, t2_2.col_name as name2
from
table1
left join table2 t2_1 on table1.id = t2_1.id
left join table2 t2_2 on table1.id = t2_2.id
and t2_2.id2 > t2_1.id2



--John [rainbow]
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)
 
well, yeah i thought about this kind of condition too
but for whatever reason, using this condition the complete query (about 15 joined tables ;-)) returns still 2 rows for the described scenario, but the second one has NULLs as values for the second copy of "table2"

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top