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
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