CODEWITHSUNDEEP
sql
Jerry
Jerry

Reputation: 2567

SQL Self-join, Not Exists, Or Something Else?

many-to-many name and role table --

create table t (name varchar, role varchar) ; 

insert into t (name, role) values ('joe', 'husband'), ('joe', 'father'),
    ('tom', 'husband'), ('neo', 'bachelor') ; 

> select * from t;
 name |   role   
------+----------
 joe  | husband
 joe  | father
 tom  | husband
 neo  | bachelor

need to convert into mapping of name and the role(s) he does not have --

not_a    | name
---------+-----------
husband  | neo
father   | tom
father   | neo
bachelor | joe
bachelor | tom

How to achieve that in true SQL without iterating through each role/name?

Upvotes: 0

Views: 1291

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

To get roles that someone doesn't have is a little complicated. You have to generate all pairs of names and roles and then pick out the ones that don't exist. This uses a left outer join.

The following is standard SQL for doing this:

select r.role as not_a, n.name
from (select distinct name from t) n cross join
     (select distinct role from t) r left outer join
     t
     on t.name = n.name and t.role = r.role
where t.name is null;

As a note: never use varchar() without a length when defining variables and columns. The default values may not do what you expect.

Upvotes: 2

GarethD
GarethD

Reputation: 69789

Assuming you only have this table you can use:

SELECT  r.role AS not_a, n.Name
FROM    (SELECT DISTINCT Name FROM T) AS n
        CROSS JOIN (SELECT DISTINCT Role FROM T) AS r
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    t 
            WHERE   t.Name = n.Name
            AND     t.Role = r.Role
        );

Example on SQL Fiddle

The main query will generate all pairs of names/roles, then the not exists will exlcude all the pairs that already exist.

If you actually have a name and role table, then you can replace the subqueries with the actual tables:

SELECT  r.role AS not_a, n.Name
FROM    Names AS n
        CROSS JOIN Roles AS r
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    t 
            WHERE   t.Name = n.Name
            AND     t.Role = r.Role
        );

You haven't specified a DBMS, so if you are using MySQL, using LEFT JOIN\IS NULL will perform better than NOT EXISTS

SELECT  r.role AS not_a, n.Name
FROM    (SELECT DISTINCT Name FROM T) AS n
        CROSS JOIN (SELECT DISTINCT Role FROM T) AS r
        LEFT JOIN t
            ON t.Name = n.Name
            AND t.Role = r.Role
WHERE   t.Name IS NULL;

I am also assuming it was just a demo, but in your table DDL you have used VARCHAR without a length which is not a good idea at all

Upvotes: 2

Related Questions