select items that have multiple matches in a normalized table

I have a table that stores a bunch of objects. Each object can have many colors which are stored in a normalized table connected by the object_id.

If I go

SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` IN ('red', 'blue');

Then I get objects that are 'red' OR 'blue'. I need to get all objects that are 'red' AND 'blue'. If I go:

SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` = 'red' AND `object_color` = 'blue';

Then I get nothing as there's only one object_color in each line and it can't be both. Also, in actuality, the colors are id's with names in another table. I simplified everything here for the sake of the question.

I need to be able to search for an unlimited number of colors.开发者_高级运维

Thanks

EDIT:

object_color is only in the object_color table.

And any object will have any single color once.

---

Select object_name
From objects
Where object_color In('red','blue')
Group By object_name
Having Count(Distinct object_color) = 2

Btw, you never mention from which table the object_color column derives. If it is from the object_color table:

Select O.object_name
From objects As O
    Join object_color As C
        On C.object_id = O.object_id
Where C.object_color In('red','blue')
Group By O.object_name
Having Count(Distinct C.object_color) = 2

---

The above query assumes that a given object row could have not have multiple object_color rows of the same color. However, as Joel C noted, if it were possible for an object to have multiple object_color rows of red or blue, then that requires a different query. :

Select ...
From objects As O
Where O.object_id In    (
                        Select C1.object_id
                        From object_color As C1
                        Where C1.object_color = 'red'
                        )
    And O.object_id In  (
                        Select C1.object_id
                        From object_color As C1
                        Where C1.object_color = 'blue'
                        )

Yet another solution:

Select O.object_name
From objects As O
    Join    (
            Select C1.object_id, C1.object_color
            From object_color As C1
            Where C1.color In('red','blue')
            Group By C1.object_id, C1.object_color
            ) As Z
        On Z.object_id = O.object_id
Group By O.object_name
Having Count(*) = 2

---

I prefer ON rather than USING:

SELECT o.object_name
FROM objects o
  JOIN object_color oc
    ON o.object_id = oc.object_id
WHERE oc.object_color IN ( 'red', 'blue' )
GROUP BY o.object_id
HAVING COUNT(o.object_id) = ( SELECT COUNT(*)
                              FROM ( 'red', 'blue' )
                            )

Assuming that it is not possible for an object to have many rows with same colour.

---

You'll need a multi join

SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` = 'red'
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` = 'blue' ;

---

You can also use INTERSECT if it is supported in the SQL you're using.

SELECT object_name
FROM objects o, objects_color oc
WHERE object_color = 'red'
and o.object_id = oc.object_id

INTERSECT

SELECT object_name
FROM objects o, objects_color oc
WHERE object_color = 'blue'
and o.object_id = oc.object_id

This will intersect the two tables and only show rows that have both red and blue color matched with the same object name.