-1

With this simple query I can find the ids of all jars that contain either red balls or blue balls:

SELECT id FROM "Jars" J
JOIN "Balls" B ON B.jarId = J.jarId
WHERE B.color = 'red' OR B.color = 'blue'
GROUP BY id

How can I find the ids of all jars that contain both red balls and blue balls?

Jazcash
  • 3,145
  • 2
  • 31
  • 46
  • 1
    Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names –  May 08 '21 at 12:29

2 Answers2

1

If it’s both you seek, then give this a go:

SELECT id
  FROM (
SELECT j.id,
       COUNT(CASE WHEN b.colour = 'red' THEN b.id ELSE NULL END) as reds,
       COUNT(CASE WHEN b.colour = 'blue' THEN b.id ELSE NULL END) as blues
  FROM Jars j INNER JOIN Balls b ON j.jarId = b.jarId
WHERE b.color IN ('red', 'blue')
GROUP BY j.id) tmp
 WHERE tmp.reds > 0 and tmp.blues > 0;
matigo
  • 1,321
  • 1
  • 6
  • 16
  • ty, this works, although I was hoping for a simpler answer that doesn't involve a subquery, but maybe that's not possible. – Jazcash May 08 '21 at 12:30
1

You don't need a sub-query. You don't even need the JOIN as you only want the IDs of the jars (which is available in the balls table)

select b.jarid
from balls b
where b.color in ('blue', 'red')
group by b.jarid
having count(*) = 2

Note that this will also return balls that have additional colors (e.g. blue, red and yellow).

If you want only those that have exactly the colors blue and red, you can use this:

select jarid
from balls
group by jarid
having count(*) = 2 
and bool_and(color in ('blue', 'red'))