SQL: Only output the color/colors of balls that are in every box

Question

I'm trying to solve this SQL problem, I have a dozen different colored balls and a few boxes. Now, I'm trying to write an SQL query that outputs the color of the balls that are in every box. So, if Green balls exist in Box A, B and C, but Blue and Red balls are only in 1 or 2 out of 3 boxes, I should only output Green.

I have three tables:

Box

ID  |
====|
A   |
B   |
C   |

Ball

Number | boxid | colorid |
=======|=======|=========|
1      |A      | Green   |
2      |A      | Red     |
3      |A      | Blue    |
4      |B      | Green   |
5      |B      | Red     |
6      |NULL   | Blue    |
7      |C      | Green   |
8      |NULL   | Red     |
9      |NULL   | Blue    |
10     |NULL   | Green   |
11     |NULL   | Red     |
12     |NULL   | Blue    |

NULL meaning they're not inside any of the boxes

Color

Name  |
======|
Blue  |
Red   |
Green |

Now, at first I thought I could solve this using this code:

SELECT colorid
FROM Ball
GROUP BY colorid
HAVING COUNT(colorid) = (SELECT COUNT(*) FROM Box) 
OR COUNT(colorid) > (SELECT COUNT(*) FROM Box)

But then I realized that once you give a value to the rows that have NULL on boxid it'll output those colors as well because my SQL query doesn't take into account that the colored balls have to be in all three different boxes, just that all three colored balls are in any box.

I'm stumped on how to change my query so that it only counts if the ball is in a different box than another one. Please help.

Sklivvz · Answer 1 · 2013-12-20T22:47:58.543

2

Assuming that we know that the number of distinct boxes is 3:

  SELECT colorId 
    FROM Ball
   WHERE boxid IS NOT NULL
GROUP BY colorId
  HAVING COUNT (DISTINCT boxid) = 3

Otherwise

  SELECT colorId 
    FROM Ball
   WHERE boxid IS NOT NULL
GROUP BY colorId
  HAVING COUNT (DISTINCT boxid) = SELECT COUNT (DISTINCT ID) FROM Box

If the balls can be in boxes different from the three in Box, then we need a subquery to only count the balls in the boxes we care about.

  SELECT colorId 
    FROM Ball
   WHERE EXISTS(SELECT 1 FROM Box WHERE ID = boxid)
GROUP BY colorId
  HAVING COUNT (DISTINCT boxid) = SELECT COUNT (DISTINCT ID) FROM Box

edited Dec 20 '13 at 22:47

answered Dec 20 '13 at 22:17

Sklivvz

30,601
24
116
172

I don't think I can use WHERE colorid IS NOT NULL, because the values can be changed – user3124096 Dec 20 '13 at 22:26
What do you mean? A query represents the state of the db at a certain point in time, so they can't "change" – Sklivvz Dec 20 '13 at 22:29
I mean that if someone Updates the table Ball so that every ball has a value in boxid, then that query won't work. – user3124096 Dec 20 '13 at 22:32
why not? it will give a different result because more balls are in boxes – Sklivvz Dec 20 '13 at 22:33
Wait, I was confused. colorid can't be NULL, only boxid can be NULL. Is that WHERE clause necessary? – user3124096 Dec 20 '13 at 22:42
@user3124096 right, i confused boxes and colors. it's now boxid (which can be null) – Sklivvz Dec 20 '13 at 22:44

score 0 · Answer 2 · answered Dec 20 '13 at 22:21

0

You could first create a Cross Join of possible combinations and inner join to that, grouping by colour.

answered Dec 20 '13 at 22:21

Preet Sangha

64,563
18
145
216

Chains · Answer 3 · 2013-12-20T22:40:21.317

0

Using INNER JOINs...to find colors that are in all 3 boxes

SELECT DISTINCT
   colorID
FROM
   Ball

   INNER JOIN (SELECT boxID, colorID FROM Ball WHERE boxID = 'A') A
   ON Ball.colorID = A.colorID

   INNER JOIN (SELECT boxID, colorID FROM Ball WHERE boxID = 'B') B
   ON Ball.colorID = B.colorID

   INNER JOIN (SELECT boxID, colorID FROM Ball WHERE boxID = 'C') C
   ON Ball.colorID = C.colorID

edited Dec 20 '13 at 22:40

answered Dec 20 '13 at 22:34

Chains

12,541
8
45
62

Is it possible to modify this so that it would work even if I inserted a new ID to the table Box(meaning I add another box)? It seems like a have to add an INNER JOIN for each Box. – user3124096 Dec 20 '13 at 23:02

SQL: Only output the color/colors of balls that are in every box

3 Answers3