Re: SQL Intersect like problem

Bruno,

I am worried about the performance and scaleability of using a sequence
of subqueries.  What bothers me is, if there are say 20-30 line items
of productid and quantities then the sql that results might get to
be too much for postgresql to handle. I would like to know if there
are limitations on the number of subqueries that postgres can handle.


Sorry for not replying to the list, I hit reply instead of reply-all :-(

Thanks,
Dinesh
At Wednesday, 5 February 2003, Bruno Wolff III <bruno@wolff.to> wrote:

>On Wed, Feb 05, 2003 at 10:17:09 -0500,
>  Dinesh Visweswaraiah <dinesh@trailblazingsolutions.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.
>
>---------------------------(end of broadcast)-------------------
--------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.
org
>



Dinesh
Cell:703-725-4153
Email:dinesh@trailblazingsolutions.com
Web Site:http://www.trailblazingsolutions.com/dinesh

The significant problems we face cannot be solved by the same level
of thinking that created them  - Albert Einstein