Q: sql select where criteria for each row is not wholly contained in the row
I'll assume that it is possible for 1 invoice to contain at least 2 details
lines, one for each product. If you want a list of invoice id's that
contain the two products( one or the other, possibly both ), this might
work:
select invoice_id
from invoice_detail
where product_id in( '002', '005' )
group by invoice_id
Avoid selecting all detail and group the result set, so that you only get
what you need.
Quote:
>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
