SQL Query Error: "sub-select returns 2 columns - expected 1" when trying to match either of two columns of subquery

Question

When I write the following query:

SELECT name
FROM people
WHERE phone_number IN 
(SELECT caller, receiver FROM phone_calls WHERE month = 7 AND day = 28 AND year = 2020 AND duration < 60);

I get the following error: sub-select returns 2 columns - expected 1

Not sure where the issue is... caller is TEXT with phone number.
receiver is TEXT with phone number. My goal is to create a list where instead of showing me to caller and receiver's phone number, it'll show me their names instead.

Also, this is my attempt at CS50's fiftyville.

Answer 1

What part of the error do you not understand? The subquery for an in can only have one column.

In your case, I think you want either column to match. So, use exists instead:

SELECT p.name
FROM people p
WHERE EXISTS (SELECT 1
              FROM phone_calls pc
              WHERE pc.month = 7 AND pc.day = 28 AND pc.year = 2020 AND
                    pc.duration < 60 AND
                    p.phone_number IN (pc.caller, pc.receiver)
             );

Answer 2

Couldn't you just use a JOIN instead of the subquery in this case? Something along:

SELECT name
FROM people
INNER JOIN phone_calls
  ON people.phone_number IN (
    phone_calls.caller,
    phone_calls.receiver
  )
  AND phone_calls.month = 7
  AND phone_calls.day = 28
  AND phone_calls.year = 2020
  AND phone_calls.duration < 60

This wouldn't work however with UPDATE/DELETE, as those don't support JOIN in SQLite unfortunately, in those cases I don't know how to avoid the correlated subquery as proposed by Gordon:

• Update with Join in SQLite
• How delete table inner join with other table in Sqlite?