Q: sql query where criteria is not all contained in each row

From: john h <jj_90_at_hotmail.com>
Date: Sun, 02 Aug 1998 14:03:00 -0400
Message-ID: <35C4A9D4.7604612D@hotmail.com>

hi,

I'm having trouble finding an elegant solution to a problem using sql.

I'm trying to run a query to select a set of rows from a table, but the criteria for each row is not wholly contained in the row. Here are the details:

Table: invoice_detail

  invoice_detail_id   (PK)
  invoice_id          (FK)
  product_id          (FK)

quantity

So, for each product that an invoice contains, there is a row in this table. Now, my problem is how do i select All invoices that contain the products 002 _and_ 005.

SELECT * FROM invoice_detail WHERE product_id = 002 AND product_id = 005

obviously won't work, since product_id cannot be two values at once. I realize that there probably needs to be two separate queries with some sort of join but am stuck. Any tips or pointers would be greatly appreciated!

-john
Received on Sun Aug 02 1998 - 13:03:00 CDT