Chris A
Chris A

Reputation: 1090

I want to return multiple rows in a subquery

Purpose

Make a query which finds missing combinations based on two tables.

Example

letter table

letter
======
a  
b  
c  
d  
e

nameletter table

name    letter  
=====   ======
chris   a  
chris   b  
chris   d  
james   b  
james   d  

Expected outcome

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.

SQL to recreate my tables

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions