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
)