Has them all

A question that pops up frequently on Devshed forums is "How can I get all products that are available in Red and Green colors?" or "How can I find out which customers bought this book and that CD?", solution is simple and I'll provide an example here, it can be made more complicate at your option, but it all boils down to a where and an having condition.
Say we have a table that lists all products and the colors in which those products are available:

CREATE TABLE PRODUCT_COLORS(
  PRODUCT_CODE CHAR(5) NOT NULL,
  COLOR_CODE CHAR(1) NOT NULL,
  CONSTRAINT PRODUCT_COLORS_PK PRIMARY KEY (PRODUCT_CODE,COLOR_CODE)
); 

Data looks like:

Code:

XXXXX                R
XXXXX                B
YYYYY                Y
YYYYY                G
ZZZZZ                G
ZZZZZ                R   

First column is the product code, second column is the color code.
Say we want to know which products are available in G(reen) and R(ed), the query is simple, we'll list all products which do have the Red or Green option and then filter out all those that don't have both, getting the desired result (in this case product 'ZZZZZ')
See it in action:

SELECT a.PRODUCT_CODE
FROM PRODUCT_COLORS a
WHERE a.COLOR_CODE IN ('R', 'G')
GROUP BY a.PRODUCT_CODE
HAVING COUNT(a.PRODUCT_CODE) = 2 

See where I implemented the two conditions? One in the where clause and the second pass to filter out all products which don't have both in the having clause.
Example is built on Firebird, but should work in MySQL, PostgreSQL or any other mainstream database too.