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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 02 Aug 1998 19:32:03 GMT
Message-ID: <35cbbd77.15418200@192.86.155.100>

A copy of this was sent to john h <jj_90_at_hotmail.com> (if that email address didn't require changing) On Sun, 02 Aug 1998 14:03:00 -0400, you 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.
>
>SELECT * FROM invoice_detail WHERE product_id = 002 AND product_id = 005
>

If I understand your question, then the query:

select invoice_id
  from invoice_detail
 where product_id in ( '002', '005' )

 group by invoice_id
having count(*) >= 2;

should do it. It will produce a list of invoice_ids that have both '002' and '005' in it.

there are other ways to write it, such as:

select invoice_id
  from invoice_detail a
 where product_id = '002'
   and exists ( select NULL 
                  from invoice_detail b
                 where b.invoice_id = a.invoice_id
                   and b.product_id = '005' )

which might work faster if there is an index on (product_id,invoice_id) -- the index would have to be in that order tho.

>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Aug 02 1998 - 14:32:03 CDT