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:
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.