如何检查两列中同一个表中字段的相互存在 [英] How to check for mutual existence of Fields in same table in Two columns

问题描述

我尝试使用Exist"和IN".我不仅没有成功，而且似乎不是一个有效的解决方案.
这是一个简化的例子:

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
用户名 1  - 等级  - 用户名 2  - 年级

TblMyTable
UserName1  -  Grade  -  UserName2  -  Grade

我需要一个相互关系/存在的查询.
我的意思是，查询返回的结果将仅包括在同一行中同时存在 UserName1 和 UserName2 相互 的用户(有关更好的示例，请参见下图/解释).

任何用户都可以与任何其他用户一起工作.
因此，理想情况下，结果将是(顺序无关紧要)在一行中:
约翰 - 5000  -- 玛丽 - 3000
或者
玛丽 - 3000  -- 约翰 - 5000

重点是，它是一个动态变化的表，活动用户使用 F.Key 到主用户表，该表具有 P.Key.

请参阅下图以获得更好的示例/解释.
数据库是 SQL 2005.

提前多谢

* 希望有助于解释这一切的截图.
最终结果应该是 2 行，因为只有它们在 TblDynamicUserList 中有相互关系:

ana - 电话 - 3000   ---  RanAbraGmail - Wifi - 2000
和
anaHeb - 电话 - 5000   ---  RoyP - 电话 - 4000
http://www.marketing2go.co.il/SqlQuestion.jpg

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

* Screenshot that hopefully help 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

推荐答案

这样的事情对你有用:

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:鉴于问题的更新，以下查询如何:

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

这篇关于如何检查两列中同一个表中字段的相互存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！