How to check for mutual existence of Fields in same table in Two columns

I tried using "Exist" and "IN". Not only did I not succeed, it didn't seem as an efficient solution.

Here is a simplified example:

TblMyTable

UserName1  -  Grade  -  UserName2  -  Grade

I need a query where there is a mutual relation / existence.

What I mean is that the returned result from the query will only include the users where on the same row there is both UserName1 and UserName2 mutually (see image below for a better example / explanation).

Any user can work with any other user.

So the result will be (the order doesn't matter) ideally in one line:

John - 5000  --  Mary - 3000

or

Mary - 3000  --  John - 5000

The punchline is, it's one dynamically changing table with active Users using the F.Key to the main User's table, which has the P.Key.

Please see image below for a better example / explanation.

Database is SQL 2005.

Many thanx in advance

* Edit: Screenshot that hopefully hel开发者_运维知识库p explain it all.

The end result should be 2 rows, because only they have a mutual relation in TblDynamicUserList:

ana - Phone - 3000   ---   RanAbraGmail - Wifi - 2000

and

anaHeb - Phone - 5000   ---   RoyP - Phone - 4000

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

---

Would something like this work for you:

With ManagerWorkers As
    (
    -- get managers with workers
    Select Managers.WorkerUsername As ManagerUsername, Workers.WorkerUsername
    From tblMyTable As Managers
        Join tblMyTable As Workers
            On Workers.ManagerUsername = Managers.WorkerUsername
    ) 
Select *
From ManagerWorkers
Union All
-- get workers that have a manager in the above list
Select WorkerUsername, ManagerUsername
From tblMyTable
Where Exists(   Select 1
                From ManagerWorkers
                Where ManagerWorkers.ManagerUsername = tblMyTable.ManagerUsername
                )   

EDIT: Given the update to the question how about the following query:

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

---

This is a bit involved of a query but works as requested. It basically joins the two tables twice and then compares where there are matches on both sides. The comparison clause makes sure that duplicates are not returned by the "NOT" in the final where clause.

SELECT F.UserU, F.UserV
FROM
  (SELECT U.u_userName AS UserU, V.u_username AS UserV
    FROM TblUsers U INNER JOIN TblDynamicUserList D 
    ON U.u_username = D.U_username
    INNER JOIN TblUsers V On V.u_username = D.F_UserName) F,
  (SELECT U.u_userName AS UserU, V.u_username AS UserV
    FROM TblUsers U INNER JOIN TblDynamicUserList D 
    ON U.u_username = D.U_username
    INNER JOIN TblUsers V On V.u_username = D.F_UserName) G
WHERE (F.UserU = G.UserV AND F.UserV = G.UserU AND F.UserU < F.UserV) 
AND  NOT (F.UserU = G.UserV AND F.UserV = G.UserU AND F.UserU > F.UserV)