SQL: Select ID/Value combinations that do not possess a value in another table

So here's basically the issue (I'm turning this into more of a universal question in case people need something like this in the future).

I have one table ("People") that is basically this

╔══════════╦═══════╗
║ PersonID ║ Letter║
╠══════════╬═══════╣
║ 1        ║ A     ║
║ 1        ║ B     ║
║ 1        ║ C     ║
║ 1        ║ D     ║
║ 2        ║ A     ║
║ 2        ║ B     ║
║ 2        ║ C     ║
║ 3        ║ B     ║
║ 3        ║ C     ║
║ 4        ║ A     ║
║ 4        ║ C     ║
║ 4        ║ D     ║
║ 5        ║ E     ║
╚══════════╩═══════╝

And lets say I have another table ("Letters") which can lists all possible "Letters" a person can have.

╔══════════╦══════╗
║ LetterID ║ Text ║
╠══════════╬══════╣
║ 1        ║ A    ║
║ 2        ║ B    ║
║ 3        ║ C    ║
║ 4        ║ D    ║
║ 5        ║ E    ║
╚══════════╩══════╝

I need to make a new table that will have all persons listed and letters that they DONT have. So for this example, the result would be this

╔══════════╦══════════════╗
║ PersonID ║ LetterNotHad ║
╠══════════╬══════════════╣
║ 1        ║ E            ║
║ 2        ║ D            ║
║ 2        ║ E            ║
║ 3        ║ A            ║
║ 3        ║ D            ║
║ 3        ║ E            ║
║ 4        ║ B            ║
║ 4        ║ E            ║
║ 5        ║ A            ║
║ 5        ║ B            ║
║ 5        ║ C            ║
║ 5        ║ D            ║
╚══════════╩══════════════╝

Any and all help or guidance is greatly appreciated.

Edit: Here's basically what I was trying, something like this

select p.PersonId, l.value
from letters l
left join people p
on l.Text = p.Letter
where p.personid is null

Answers


Here is the idea

WITH cte 
     AS (SELECT * 
         FROM   (SELECT DISTINCT personid 
                 FROM   people) B 
                CROSS JOIN (SELECT DISTINCT Text as letter 
                            FROM   letters) A) 
SELECT * 
FROM   cte c 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   first_table f 
                   WHERE  c.personid = f.personid 
                          AND c.letter = f.letter) 

Note: You need to use letterid in People table instead of Letter and define a foreign key which make the table's consistent


So what you want to do in order to find the missing values is to generate the set that represents all possible values. This is the cartesian product between the two sets (people and letters) and in SQL you use the cross join operator (or unqualified join) to do this.

From this set you want to remove the combinations you already have, and the remainder will be the missing ones.

There are many ways to do this; using a left join it could look like this:

select sub.* 
from (
    select distinct personid, text 
    from people 
    cross join letters
) sub
left join people p on p.letter = sub.text and p.personid = sub.personid
where p.personid is null

Or using the except set operator (for MSSQL (minus in Oracle) - MySQL does not have this):

select personid, text from people cross join letters
except 
select personid, letter from people

Need Your Help

validate with "has_many :through"

ruby-on-rails validation orm associations

I have validate :my_validate in my User model, but my_validate is never called if "has_may :through" attribute is changed. Is it right behavior?

getline() function is skipping inputs

c++ cin getline

I have the following code that does some tests on nomC and nomP, but using 2 successive getlines causes skipping the the first one (getline(cin,nomP); ) .. how can i fix the problem ? PS : i tried ...