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

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 existi开发者_如何学运维ng 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 several trials I commented out 2 lines (Below).

The returned results are what I am trying to accomplish, as described in the beginning of this question, except for 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.

http://www.marketing2go.co.il/SqlQuestion.jpg

Here are the results of the updated query (with the 2 lines commented out)

http://www.marketing2go.co.il/QueryResults.jpg

The required results should be without rows 1 and 2 (or without rows 3 and 5), because they are in effect, a duplicity. That is because that pair exist in rows 3 and 5 (the order of the names in each pair is irrelevant).

For a lack of a better word, it's more like a logical duplicity.

Many thanx in advance

---

Ok, as I understand it, if the results already include the pairing (a,b), they shouldn't include (b,a). Is that right? If so, that means that the root cause of your problem is that the tblDynamicUserList table includes both (a,b) and (b,a) pairings. If we filter those in the query first, then the joined results will be what you expected. Note that this also means that you don't care which username is in u_username and which is in f_username; if that mattered, then you would need both in the results.

Here is a script demonstrating your current behaviour. Note that the second query in your question didn't produce the results you posted. I have updated it to what I assume you're using:

declare @tblUsers table (id int, u_username varchar(20), permission varchar(10), experience int, grade int)
declare @tblDynamicUserList table (u_username varchar(20), f_username varchar(20), f_timestamp datetime default (getdate()))

insert into @tblUsers values (1, 'RanAbraGmail', 'WiFi', 1, 2000)
insert into @tblUsers values (2, 'Ana', 'Phone', 2, 3000)
insert into @tblUsers values (3, 'RoyP', 'Phone', 5, 4000)
insert into @tblUsers values (4, 'anaHeb', 'Phone', 14, 5000)
insert into @tblUsers values (7, 'Sheleg', 'Phone', 15, 5500)

insert into @tblDynamicUserList values ('ana', 'RanAbraGmail', default)
insert into @tblDynamicUserList values ('anaHeb', 'RoyP', default)
insert into @tblDynamicUserList values ('RanAbraGmail', 'Ana', default)
insert into @tblDynamicUserList values ('RanAbraGmail', 'RoyP', default)
insert into @tblDynamicUserList values ('RoyP', 'anaHeb', default)
insert into @tblDynamicUserList values ('Sheleg', 'RanAbraGmail', default)

Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Grade
from @tblDynamicUserList As D1
    Join @tblUsers As U1
        On U1.u_username = D1.u_username
    Join @tblUsers As U2
        On U2.u_username = D1.f_username

Therefore, this query will produce the results you want, by first retrieving only the distinct pairings of usernames, and only then joining to the users table to retrieve the details.

Select D1.username1, U1.Permission, U1.Grade, D1.username2, U2.Permission, U2.Grade
from (select distinct case when u_username < f_username then u_username else f_username end as username1,
    case when u_username < f_username then f_username else u_username end as username2
    from @tblDynamicUserList) As D1
    Join @tblUsers As U1
        On U1.u_username = D1.username1
    Join @tblUsers As U2
        On U2.u_username = D1.username2