来自具有多个组的表的SQL值

时间:2021-01-21 07:32:03

I have a table that has multiple persons linked to multiple groups.

我有一个表,有多个人链接到多个组。

I have a procedure that needs to bring everyone from group 75588 BUT if this person if in group 2443 it can't bring. I'm trying something like this.

我有一个程序需要从75588组中带来所有人但是如果这个人如果在2443组中它不能携带。我正在尝试这样的事情。

SELECT * FROM BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo WHERE UXG_N_USU_N_CODIGO = 302826
AND ((UXG_N_GRP_N_CODIGO <> 2443) AND (UXG_N_GRP_N_CODIGO = 75588))

Exemple: Person A Is in one group, the group 75588 Person B Is in twogroups, the group 75588 and 2443

例如:人A在一组中,组75588人B在两组中,组75588和2443

In the select only person A has to return.

在仅选择人A必须返回。

But its not work,it is bring all the values.

但它不起作用,它带来了所有的价值观。

2 个解决方案

#1


1  

Replace person_id in the following example with the column that identifies individual people.

将以下示例中的person_id替换为标识各个人的列。

using not exists():

使用not exists():

select *
from BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo as o
where UXG_N_USU_N_CODIGO = 302826
  and UXG_N_GRP_N_CODIGO = 75588
  and not exists (
    select 1
    from BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo as i
    where i.person_id = o.person_id
      and i.UXG_N_GRP_N_CODIGO = 2443
    )

#2


0  

Make the following changes :

进行以下更改:

  • Use NOT EXISTS to stop records selection for 2443.
  • 使用NOT EXISTS停止2443的记录选择。
  • Use Unique field like primary field to map reference between person and group tables.
  • 使用像主字段这样的唯一字段来映射人员和组表之间的引用。

You query should like this :

您的查询应该是这样的:

SELECT * FROM BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo AS A
WHERE A.UXG_N_GRP_N_CODIGO = 75588 AND A.UXG_N_USU_N_CODIGO = 302826
AND NOT EXISTS (
SELECT 0 FROM BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo AS B
WHERE B.UXG_N_GRP_N_CODIGO = 2443 AND B.PID = A.PID
)

#1


1  

Replace person_id in the following example with the column that identifies individual people.

将以下示例中的person_id替换为标识各个人的列。

using not exists():

使用not exists():

select *
from BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo as o
where UXG_N_USU_N_CODIGO = 302826
  and UXG_N_GRP_N_CODIGO = 75588
  and not exists (
    select 1
    from BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo as i
    where i.person_id = o.person_id
      and i.UXG_N_GRP_N_CODIGO = 2443
    )

#2


0  

Make the following changes :

进行以下更改:

  • Use NOT EXISTS to stop records selection for 2443.
  • 使用NOT EXISTS停止2443的记录选择。
  • Use Unique field like primary field to map reference between person and group tables.
  • 使用像主字段这样的唯一字段来映射人员和组表之间的引用。

You query should like this :

您的查询应该是这样的:

SELECT * FROM BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo AS A
WHERE A.UXG_N_GRP_N_CODIGO = 75588 AND A.UXG_N_USU_N_CODIGO = 302826
AND NOT EXISTS (
SELECT 0 FROM BRZGRUDB08.BrinksPortal.dbo.tuxg_usuario_grupo AS B
WHERE B.UXG_N_GRP_N_CODIGO = 2443 AND B.PID = A.PID
)