Reputation: 1090
Make a query which finds missing combinations based on two tables.
letter
======
a
b
c
d
e
name letter
===== ======
chris a
chris b
chris d
james b
james d
Want to find all the name/letter combinations which currently don't exist
name letter
===== ======
chris c
chris e
james a
james c
james e
Tried the following SQL
select name, (select letter
from letters where letter not in
(select letter from nameletter nl2 where nl2.name = nl.name))
from nameletter nl
But got the error message that the subquery returned more than 1 value.
create table letters (letter varchar(1))
insert into letters
values ('a'),('b'),('c'),('d'),('e')
create table nameletter (name varchar(max),letter varchar(1))
insert into nameletter values
('chris', 'a'),('chris', 'b'),('chris', 'd'),('james', 'b'),('james','d')
Upvotes: 4
Views: 61
Reputation: 1269773
Use a cross join
to generate the rows, and then left join
(or not in
or not exists
) to get the unmatched ones:
select n.name, l.letter
from (select distinct name from nameletter) n cross join
letter l left join
nameletter nl
on nl.name = n.name and nl.letter = l.letter
where nl.name is null;
Upvotes: 3