SQL - Select pairs of users with different group ID

user_9170190 0
Viewed 72

I have to write a query in SQL that returns pairs of users (name and surname) that are never associated together in the same group.

GROUP

idGroup
idUser (Foreign key)

USER

idUser (Primary key)
Name 
Surname

I tried this but it don't work, why?

SELECT u1.name, u1.surname, u2.name, u2.surname 
FROM group g1
JOIN group g2 ON g2.idgroup = g1.idgroup
JOIN user u1  ON u1.userid = g1.iduser
JOIN user u2  ON u2.userid = g2.iduser
WHERE g2.idgroup <> g1.idgroup
oracle sql
3 Answers

Your attempt fails because it just checks if the pair of users has at least one group not in common, while you want to check if all groups are distinct.

You can self-join the users table to generate all possible pairs of users, and then use not exists with an aggregate correlated subquery to ensure that no group exists where both users appear:

select u1.name name1, u1.surname surname1, u2.name name2, u2.surname surname2
from users u1
inner join users u2 on u1.id_user < u2.id_user
where not exists (
    select 1
    from groups g
    group by g.id_group
    having 
            max(case when g.id_user = u1.id_user then 1 end) = 1 
        and max(case when g.id_user = u2.id_user then 1 end) = 1
)
user_106767160

I am thinking not exists:

select u1.iduser, u2.iduser
from users u1 join
     users u2
     on u1.iduser < u2.iduser
where not exists (select 1
                  from usergroups ug1 join
                       usergroups ug2
                       on ug1.idgroup = ug2.idgroup
                  where ug1.iduser = u1.iduser and
                        ug2.iduser = u2.iduser
                 );
user_11440350

After all it's just all two-users combinations except for those found in a group. We mainly have MINUS, NOT EXISTS and NOT IN for this.

MINUS

select u1.iduser, u2.iduser from "USER" u1 join "USER" u2 on u1.iduser < u2.iduser
minus
select g1.iduser, g2.iduser from "GROUP" g1 join "GROUP" g2 on g1.idgroup = g2.idgroup 
                                                           and g1.iduser < g2.iduser

NOT IN

select u1.iduser, u2.iduser from "USER" u1 join "USER" u2 on u1.iduser < u2.iduser
where (u1.iduser, u2.iduser) not in
(
  select g1.iduser, g2.iduser from "GROUP" g1 join "GROUP" g2 on g1.idgroup = g2.idgroup 
                                                             and g1.iduser < g2.iduser
)

NOT EXIST

select u1.iduser, u2.iduser from "USER" u1 join "USER" u2 on u1.iduser < u2.iduser
where not exists
(
  select null from "GROUP" g1 join "GROUP" g2 on g1.idgroup = g2.idgroup 
  where g1.iduser = u1.iduser
    and g2.iduser = u2.iduser
)

As you see, the three queries don't differ much. While the MINUS query looks simplest, it has the disadvantage that you cannot directly display the users' names, if you want to. EXISTS subqueries look always a tad more complicated than IN subqueries. (NOT IN holds a trap, though, when values can be null, which is not the case here.) My choice would be NOT IN here, but just pick what you like best.

(On a side note: USER and GROUP are both SQL keywords and musthence not a good choice for table names in my opinion.)

user_22707620
Related