Selecting all rows for names that satisfy condition

Question

I always come across these kinds of SQL problems and find it confusing what to do. For example, suppose there is a table of : names, year, food.

`CREATE TABLE t (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    food VARCHAR(50)
);
INSERT INTO t (name, food) VALUES
('john', 'pizza'),
('john', 'cake'),
('andrew', 'pizza'),
('andrew', 'pizza'),
('andrew', 'pizza'),
('matt', 'pizza'),
('matt', 'pizza'),
('matt', 'burger'),
('david', 'cake'),
('david', 'pizza'),
('david', 'pizza'),
('elen', 'cake'),
('elen', 'pizza'),
('elen', 'donuts'),
('claire', 'cake'),
('claire', 'donuts'),
('claire', 'tacos'),
('john', 'pizza'),
('john', 'cake'),
('matt', 'apples'),
('matt', 'tacos');

` If a name has some rows that meet a certain condition, I want to select all rows and all columns for that name (even the rows that for that name that don't directly match). For example:

• Problem 1 Select all rows/columns for names that ONLY like pizza.

Types of rows that would be selected:

 john : pizza ;
 andrew: pizza, pizza, pizza 

Types of rows that would not be selected:

 matt: pizza, pizza, burger

• Problem 2: Select all rows/columns for names that ONLY like pizza and cake

Types of rows that would be selected:

 john: cake, pizza ;
 david: cake, pizza, pizza

Types of rows that would not be selected:

elen : cake, pizza, donuts

• Problem 3: Select all rows/columns for names that DONT like pizza at all

Types of rows that would be selected:

claire: cake, donuts, tacos

Types of rows that would be not be selected:

 john: pizza, cake 

• Problem 4: Select all rows/columns for names that DONT like pizza and cake at all

Types of rows that would be selected:

matt : apples, tacos

Types of rows that would not be selected:

john : cake, apples, donuts;
elen: pizza, chocolate;
tyler: pizza, cake;

I never how to write the query for these problems.

For example:

Problem 1:

SELECT *
FROM t
WHERE name IN (
  SELECT name
  FROM t
  WHERE food IN ('pizza')
  GROUP BY name
  HAVING COUNT(DISTINCT food) = 1 AND MIN(CASE WHEN food NOT IN ('pizza') THEN 1 ELSE 0 END) = 0
);

Problem 2:

SELECT *
FROM t
WHERE name IN (
  SELECT name
  FROM t
  WHERE food IN ('pizza', 'cake')
  GROUP BY name
  HAVING COUNT(DISTINCT food) <= 2 AND MIN(CASE WHEN food NOT IN ('pizza', 'cake') THEN 1 ELSE 0 END) = 0
);

Problem 3:

SELECT *
FROM t
WHERE name NOT IN (
  SELECT name
  FROM t
  WHERE food IN ('pizza')
);

Problem 4:

SELECT *
FROM t
WHERE name NOT IN (
  SELECT name
  FROM t
  WHERE food IN ('pizza', 'cake')
);

I get so confused when writing these SQL statements, thinking about which conditions are needed to correctly identify the logical conditions. Is there a way to simplify the logic for these types of queries?

Answer 1

I think queries 3 and 4 are perfect like that, and what you need to do in 1 and 2 is try to think more in terms of sets, like in 3 and 4, instead of trying to find conditions based on counts of rows.
Here's how I would do them

Problem 1:

Basically select all the names with food pizza, and that are not included in the set of names with any other food. If I understood you correctly we should only get andrew here (because jonh also liked cake)

select * 
from t 
where food = 'pizza' 
and not exists (
select 1 from t tnp where tnp.food <> 'pizza' and tnp.name = t.name
);

Problem 2:

Select all names that are included in the set of names that liked pizza and/or cake, and that are not included in the set of names that liked something different than cake/pizza

select * 
from t 
where 
exists (select 1 from t tpc where tpc.food in ('pizza', 'cake') and tpc.name = t.name) 
and not exists (select 1 from t tot where tot.food not in ('pizza', 'cake') and tot.name = t.name);

Be careful in problem 2, the query above is for names that liked pizza AND/OR cake, and nothing else. If you wanted to get names that liked pizza AND cake and nothing else (eliminating andrew, who liked pizza but not cake), then you would have to add the condition that both pizza and cake should exist in the foods:

select * 
from t 
where 
exists (select 1 from t tp where tp.food in ('pizza') and tp.name = t.name) 
and exists (select 1 from t tc where food in ('cake') and tc.name = t.name) 
and not exists (select 1 from t tot where tot.food not in ('pizza', 'cake') and tot.name = t.name);

---

If you are not used to the exists/not exists operators you can easily change the queries to use in/not in instead:

Problem 1:

select * 
from t where food = 'pizza' 
and name not in (select name from t tnp where tnp.food <> 'pizza');

Problem 2:

select * 
from t 
where 
name in (select name from t tpc where tpc.food in ('pizza', 'cake')) 
and name not in (select name from t tot where tot.food not in ('pizza', 'cake'));

Answer 2

If you create a cte that lists food per name - you can get answers to all 4 questions simply by changing where conditions.

--    S a m p l e    D a t a :
CREATE TABLE t (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    food VARCHAR(50)
);
INSERT INTO t (name, food) VALUES
('john', 'pizza'), ('john', 'cake'),
('andrew', 'pizza'), ('andrew', 'pizza'), ('andrew', 'pizza'),
('matt', 'pizza'), ('matt', 'pizza'), ('matt', 'burger'), ('matt', 'apples'), ('matt', 'tacos'), 
('david', 'cake'), ('david', 'pizza'), ('david', 'pizza'),
('elen', 'cake'), ('elen', 'pizza'), ('elen', 'donuts'),
('claire', 'cake'), ('claire', 'donuts'), ('claire', 'tacos'),
('john', 'pizza'), ('john', 'cake');

--  cte that lists distinct food per names
WITH
  names_foods as
    ( Select  name, string_agg(distinct food, ',') as food_list
      From t
      Group By name )
/*    R e s u l t : 
name    food_list
------- -------------------------------
andrew  pizza
claire  cake,donuts,tacos
david   cake,pizza
elen    cake,donuts,pizza
john    cake,pizza
matt    apples,burger,pizza,tacos       */

The code for 1st question would be like below...

WITH
  names_foods as
    ( Select  name, string_agg(distinct food, ',') as food_list
      From t
      Group By name )
/*   M a i n    S Q L :  */
/*   1.  only pizza */
Select t.* 
From t
Inner Join names_foods f ON(f.name = t.name)
Where f.food_list = 'pizza';
/*   R e s u l t :
id  name    food
--  ------- ------------
3   andrew  pizza
4   andrew  pizza
5   andrew  pizza       */

... and for all other questions just change the WHERE clause ...

/*  2.  only pizza and cake */
...
Where f.food_list = 'cake,pizza';
/*
id  name    food
--  ------- -----------
1   john    pizza
2   john    cake
11  david   cake
12  david   pizza
13  david   pizza
20  john    pizza
21  john    cake        */  

/*  3. no pizza */
....
Where position('pizza' in f.food_list) = 0;
/*
id  name    food
--  ------- ------------
17  claire  cake
18  claire  donuts
19  claire  tacos       */

/*  4. no pizza no cake  */
...
Where position('pizza' in f.food_list) = 0 and
      position('cake' in f.food_list) = 0;
/*
no rows selected    */

If you are unfamiliar with cte-s then use it as subquery...

Select t.* 
From t
Inner Join ( Select  name, string_agg(distinct food, ',') as food_list
             From t
             Group By name ) f ON(f.name = t.name)
Where f.food_list = 'pizza';  -- for question  1
Where f.food_list = 'cake,pizza';  -- for question 2
Where position('pizza' in f.food_list) = 0;  -- for question 3
Where position('pizza' in f.food_list) = 0 and position('cake' in f.food_list) = 0;      -- for question 4

The same code above answers all your questions just by changing the WHERE clause...