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

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

Excuse me if my syntax is not strictly Oraclese but this seems trivial to me. If you just want the invoice header information:

SELECT ih.*
FROM invoice_header ih, invoice_detail d1, invoice_detail d2

WHERE ih.invoice_id = d1.invoice_id
  AND d1.product_id = 002
  AND ih.invoice_id = d2.invoice_id
  AND d2.product_id = 005;

If you want full invoice detail just make this a non-correlated sub-query selecting only ih.invoice_id as the target of an IN clause:

SELECT *
FROM invoice_header h, invoice_detail d WHERE h.invoice_id IN (

	SELECT ih.invoice_id
	FROM invoice_header ih, invoice_detail d1, invoice_detail d2
	WHERE ih.invoice_id = d1.invoice_id
	  AND d1.product_id = 002
	  AND ih.invoice_id = d2.invoice_id
	  AND d2.product_id = 005

Because the subquery is non-correlated the optimizer should do a passable job of making the second query efficient. Of course adding a filter to the inner query to only report for selected customer(s) is trivial also and more efficient than filtering in the outer query.

Art S. Kagel Received on Mon Aug 03 1998 - 15:05:26 CDT