Get mutually and non mutually existening Fields in same table in Two columns

Posted by ranabra on Stack Overflow See other posts from Stack Overflow or by ranabra
Published on 2010-03-15T22:56:55Z Indexed on 2010/03/15 22:59 UTC
Read the original article Hit count: 318

Filed under:
|
|
|

This is a question similar to another question I posted here but is a little different.

I am trying to get a list of all instances of mutual and non-mutual existing Users.
What I mean is that the returned result from the query will return a list of users along with their co-worker.
It is similar to the question here, but the difference is that non mutual users will be returned too and with out the "duplicity" mutually existing users return in the list (See image below in-order simplify it all).

I took the original answer from Thomas (Thanx again Thomas)
Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Grade
from tblDynamicUserList As D1
    Join tblDynamicUserList As D2
        On D2.u_username = D1.f_username
            And D2.f_username = D1.u_username
    Join tblUsers As U1
        On U1.u_username = D1.u_username
    Join tblUsers As U2
        On U2.u_username = D2.u_username

and after some several trials I commented out 2 lines (Below).
The returned result are exactly as described in the beginning of this question, but with the "duplicity" returned by mutually existing users in the table.
How can I eliminate this duplicity?

Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Grade
from tblDynamicUserList As D1
    Join tblDynamicUserList As D2
        On D2.u_username = D1.f_username
            /* And D2.f_username = D1.u_username */
    Join tblUsers As U1
        On U1.u_username = D1.u_username
    Join tblUsers As U2
        On U2.u_username = D2.u_username
/* WHERE D1.U_userName < D1.f_username */

*Screenshot that hopefully helps explain it all.

Database is SQL 2005.
Many thanx in advance

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about sql