Finding a person who has eaten apples but has not eaten bananas

I would like to device an SQLite query that finds out whether a person with a certain person_id has eaten the Fruit 'apple' but not the fruit 'banana'. I have access to a Fruit table that contains information on who has eaten which type of fruit. I would then like to use this information to SELECT the last name of the person who ate apples but not bananas with a subquery. In my mind it would go something like this:

SELECT
  P.last_name
FROM
  Person AS P
WHERE
  P.pid IN (
    SELECT
      F1.pid
    FROM
      Fruit AS F1,
      Fruit AS F2
    WHERE
      F1.pid = F2.pid
      AND F1.type = 'apple'
      AND F2.type <> 'banana'
  )
ORDER BY P.pid;

However, this is not a strict enough condition, as now I’m allowing for the same fruit type in F2 as in F1, which would still allow that person to be selected. On the other hand I can’t restrict this too much, for example with

F1.type <> F2.type

in the subquery, as that would kick out legitimate persons from the SELECTed ones.

What would the restriction have to be to make this work?

Answer

SELECT
  P.last_name
FROM
  Person AS P
WHERE
  P.pid IN (
    SELECT
      F1.pid
    FROM Fruit AS F1
      LEFT JOIN Fruit AS F2 ON F2.type = 'banana' AND F1.pid = F2.pid
    WHERE
      F1.type = 'apple'
      AND F2.pid IS NULL

  )
ORDER BY P.pid;

Advertisement

Answer