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

On 02-Aug-98 19:03:00 john h wrote:

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

Hello John,
how about the intersect operator?

Try this:

select * from invoice_detail
where invoice_id in
(select invoice_id from invoice_datail
 where product_id='002'
 intersect
 select invoice_id from invoice_detail
 where product_id='005');

This should do it.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |