SQL values from a table with multiple groups

Question

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 is in group 2443, it shouldn't. I'm trying something like this:

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))

Example: Person A is in one group, the group 75588. Person B is in two groups, the groups 75588 and 2443.

In the SELECT query, only person A is to return.

But it's not working - it is bringing all the values.

Answer 1

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

using 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
    )

Answer 2

Make the following changes :

• Use NOT EXISTS to stop records selection for 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
)