Re: SQL Intersect like problem

On Wed, Feb 05, 2003 at 10:17:09 -0500,
Dinesh Visweswaraiah <dinesh(at)trailblazingsolutions(dot)com> wrote:
> Bruno,
>
> I am sorry for being very clear. The need is to identify an InvoiceId
> from a *set* of ProductId and Quantity values. A simple join will
> not work because it is not one value of ProductId and one value of
> Quantity.

If you want all invoices that have those quantities of products, even if
there are other products, then you can just use intersect.
select invoiceid from invoicetable where productid = 'PID1' and
quantity = 'Q1'
intersect
select invoiceid from invoicetable where productid = 'PID2' and
quantity = 'Q2'
intersect
select invoiceid from invoicetable where productid = 'PID3' and
quantity = 'Q3';

If you need an exact match than you can use set difference to rule out
invoices with other product and quantity tuples.
For example, you could append the following to the above query.
except
select invoiceid from invoicetable group by invoiceid having count(*) > 3;

P.S. You normally want to keep the list copied on discussions related to your
question, rather than just replying invidiually to people that try to answer
your question.