from a table with symmetric relation select every pair only once
from a table with symmetric relation select every pair only once
(OP)
I have a table with symmetric relation, e.g. 10 is friend with 123 and 123 is friend with 10
The table looks like this:
How do I select every pair only once ?
i.e.:
The table looks like this:
CODE
with tab(personnr, personnr1) as ( values(10, 123), (123,10), (20,456), (456,20) ) select * from tab ....+....1....+....2....+....3 PERSONNR PERSONNR1 10 123 123 10 20 456 456 20 ******** End of data ********
How do I select every pair only once ?
i.e.:
CODE
PERSONNR PERSONNR1 10 123 20 456
RE: from a table with symmetric relation select every pair only once
CODE
RE: from a table with symmetric relation select every pair only once
For example the table seems like this:
CODE
i.e. the pair (30, 789) has not reverse pair (789, 30)
in that case I need to get it in my select too:
CODE
... but this query (I posted above) does not work for this case:
CODE
Have you any suggestions ?
RE: from a table with symmetric relation select every pair only once
CODE
CODE
RE: from a table with symmetric relation select every pair only once
CODE
Regards
Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com
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: from a table with symmetric relation select every pair only once
Thank you very much. It works well. The point with LEFT OUTER JOIN was an excellent idea.
But now, when I modify my first attempt above in that I use left outer join and add to the condition the alternative with IS NULL, then it seems to work too:
CODE