Thread: SQL Intersect like problem
Hello Folks, I have 7.3.1 running, and have an interesting SQL problem, please help. Consider an Invoice Table with columns InvoiceId ProductId Quantity Given a set of ProductId and Quantity, I need to find the InvoiceId that correspond to it. I can create a temp table with these 2 columns (ProductId and Quantity) if necessary. I would appreciate any help in this regard. Thanks, Dinesh
On Tue, Feb 04, 2003 at 21:00:47 -0500, Dinesh V <dinesh@trailblazingsolutions.com> wrote: > Hello Folks, > > I have 7.3.1 running, and have an interesting SQL problem, please help. > Consider an Invoice Table with columns > > InvoiceId > ProductId > Quantity > > Given a set of ProductId and Quantity, I need to find the InvoiceId that > correspond to it. I can create a temp table with these 2 columns (ProductId > and Quantity) if necessary. Unless there is more to this problem, you can just do something like: select InvoiceID from Invoice_table where ProductID = 'XXX' and Quantity = 'YYY';
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.
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
On Wed, 2003-02-05 at 16:39, Bruno Wolff III 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. Unless you are clearing these tables very frequently, it seems to me you will very soon find duplicate combinations. It is very likely (perhaps not in your case?) that customers will repeat an order with the same products and quantities as before, or that more than one customer will send in the same order. Why do you not add the invoiceid to the table with the products and quantities? This is the standard way to identify invoice lines with the invoice. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "He hath not dealt with us after our sins; nor rewarded us according to our iniquities. For as the heaven is high above the earth, so great is his mercy toward them that fear him. As far as the east is from the west, so far hath he removed our transgressions from us." Psalms 103:10-12
Hi Oliver, Actually the story is a little different. I used Invoice and Product and Quantity because everybody understands those. Reality is that this is a table that has AuthorizationSetId, UserId and Privileges. Objects in my application have a set of authorizations with specified userid and privileges, specifying who is permitted to do what. The Idea is to identify a AuthorizationSetId so that when the set of UserId and Privileges change, I can reuse a AuthorizationSetId rather than create a new one. In fact without reuse, there is no point in having a AuthorizationSetId in the first place. There will be some sets that get discarded and will be lying in the database. I was planning on having a periodic (say weekly) cleanup process that will delete these unused sets. Since these details were auxilliary to the real problem I thought maybe I can avoid confusing people. I ended up doing exactly that :-( To paraphrase the situation- Given a Master/Detail relationship. How to find a Master from the Details. Which is the reverse of what is usually done. Given a Master we find Details. Hope this clarifies things. Thanks, Dinesh At 06 February 2003, Oliver Elphick <olly@lfix.co.uk> wrote: >On Wed, 2003-02-05 at 16:39, Bruno Wolff III 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. > >Unless you are clearing these tables very frequently, it seems to me you >will very soon find duplicate combinations. It is very likely (perhaps >not in your case?) that customers will repeat an order with the same >products and quantities as before, or that more than one customer will >send in the same order. > >Why do you not add the invoiceid to the table with the products and >quantities? This is the standard way to identify invoice lines with the >invoice. > >-- >Oliver Elphick Oliver.Elphick@lfix.co.uk >Isle of Wight, UK http://www.lfix.co. uk/oliver >GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "He hath not dealt with us after our sins; nor rewarded > us according to our iniquities. For as the heaven is > high above the earth, so great is his mercy toward > them that fear him. As far as the east is from the > west, so far hath he removed our transgressions from > us." Psalms 103:10-12 > >---------------------------(end of broadcast)------------------- -------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > 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
On Thu, Feb 06, 2003 at 10:17:15 -0500, Dinesh Visweswaraiah <dinesh@trailblazingsolutions.com> wrote: > Hi Oliver, > > Actually the story is a little different. I used Invoice and Product > and Quantity because everybody understands those. Reality is that > this is a table that has AuthorizationSetId, UserId and Privileges. > Objects in my application have a set of authorizations with specified > userid and privileges, specifying who is permitted to do what. The > Idea is to identify a AuthorizationSetId so that when the set of > UserId and Privileges change, I can reuse a AuthorizationSetId rather > than create a new one. In fact without reuse, there is no point > in having a AuthorizationSetId in the first place. If you are trying to group commonly associated security rights to simplify administration, then the normal way to do this is with roles. You have a table relating users and roles and another table relating roles and security rights. By joining these two tables you get the security rights for an individual user.
Bruno, We considered roles and found it to be too rigid. We need fine grained object level rights administrated by the user rather than a fixed set administrated by the administrator. This flexibility is very important and hence Roles will be used more a hint/default rather than the actual control mechanism. regards, Dinesh At Thursday, 6 February 20037, Bruno Wolff III <bruno@wolff.to> wrote: >On Thu, Feb 06, 2003 at 10:17:15 -0500, > Dinesh Visweswaraiah <dinesh@trailblazingsolutions.com> wrote: >> Hi Oliver, >> >> Actually the story is a little different. I used Invoice and Product >> and Quantity because everybody understands those. Reality is that >> this is a table that has AuthorizationSetId, UserId and Privileges. >> Objects in my application have a set of authorizations with specified >> userid and privileges, specifying who is permitted to do what. The >> Idea is to identify a AuthorizationSetId so that when the set of >> UserId and Privileges change, I can reuse a AuthorizationSetId rather >> than create a new one. In fact without reuse, there is no point >> in having a AuthorizationSetId in the first place. > >If you are trying to group commonly associated security rights to simplify >administration, then the normal way to do this is with roles. >You have a table relating users and roles and another table relating >roles and security rights. By joining these two tables you get the >security rights for an individual user. > >---------------------------(end of broadcast)------------------- -------- >TIP 4: Don't 'kill -9' the postmaster > 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