Tuesday, July 22, 2008

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:

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

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:

  1. SELECT a.PRODUCT_CODE
  2. FROM PRODUCT_COLORS a
  3. WHERE a.COLOR_CODE IN ('R', 'G')
  4. GROUP BY a.PRODUCT_CODE
  5. 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.

2 comments:

toby said...

Can also be done using a self JOIN:

mysql> SELECT L.PRODUCT_CODE FROM PRODUCT_COLORS L JOIN PRODUCT_COLORS R ON L.PRODUCT_CODE=R.PRODUCT_CODE WHERE L.COLOR_CODE='R' AND R.COLOR_CODE='G';
+--------------+
| PRODUCT_CODE |
+--------------+
| Z |
+--------------+
1 row in set (0.00 sec)

pabloj said...

Shure, but it's more difficult to maintain if you want to let the user choose how many items mean "all"