Get mutually and non mutually existening Fields in same table in Two columns
- by ranabra
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.Gradefrom 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.Gradefrom 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
