Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id E04A2672B1D for ; Fri, 1 Jun 2012 13:59:11 -0300 (ADT) Received: from iota.marktest.pt ([80.251.174.118] helo=hermes.marktest.pt) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SaVBt-0007fu-GQ for pgsql-sql@postgresql.org; Fri, 01 Jun 2012 16:59:10 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by hermes.marktest.pt (Postfix) with ESMTP id 6F7DD1A83BA; Fri, 1 Jun 2012 17:58:55 +0100 (WEST) X-Virus-Scanned: amavisd-new at marktest.pt Received: from hermes.marktest.pt ([127.0.0.1]) by localhost (hermes.marktest.pt [127.0.0.1]) (amavisd-new, port 11124) with ESMTP id mug2XHdUHesu; Fri, 1 Jun 2012 17:58:55 +0100 (WEST) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by hermes.marktest.pt (Postfix) with ESMTP id 41FDE1A83AC; Fri, 1 Jun 2012 17:58:55 +0100 (WEST) Message-ID: From: "Oliveiros d'Azevedo Cristina" To: "Relyea, Mike" Cc: References: <1A73A33A6D424324B55B126B61DC4E2A@marktestcr.marktest.pt> <112ECE6EE7E74DE28DDDB057DB75870C@marktestcr.marktest.pt> <6C8D39EA43594F12ADF379A91DEECF59@marktestcr.marktest.pt> Subject: Re: Lowest 2 items per Date: Fri, 1 Jun 2012 17:58:54 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Pg-Spam-Score: 0.4 (/) X-Archive-Number: 201206/9 X-Sequence-Number: 36663 I only made grammatical changes necessary for the query to function (adding a missing FROM, fully qualifying "SELECT Make" as " SELECT subquery2.Make", etc.) I tried changing the join type to right and left but that did not have the desired result. * I see... If we add a query with a union that selects only the single ink printers. Something like SELECT subquery2.Make, subquery2.Model, subquery2.Color,subquery2.Type, subquery1.cpp, min(Cost/Yield) as cpp2 FROM( SELECT Printers.Make, Printers.Model, Consumables.Color, Consumables.Type, min(Cost/Yield) AS cpp FROM Printers JOIN PrintersAndConsumables ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN Consumables ON Consumables.ConsumableID = PrintersAndConsumables.ConsumableID WHERE Consumables.Cost Is Not Null AND Consumables.Yield Is Not Null GROUP BY Printers.Make, Printers.Model, Consumables.Color, Consumables.Type ) subquery1 JOIN ( SELECT Printers.Make, Printers.Model, Consumables.Color, Consumables.Type,Cost,Yield FROM Printers JOIN PrintersAndConsumables ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN Consumables ON Consumables.ConsumableID = PrintersAndConsumables.ConsumableID WHERE Consumables.Cost Is Not Null AND Consumables.Yield Is Not Null ) subquery2 ON (subquery1.Make = subquery2.Make AND subquery1.Model = subquery2.Model AND subquery1.Color = subquery2.Color AND subquery1.Type = subquery2.Type) WHERE subquery2.Cost / subquery2.Yield <> subquery1.cpp GROUP BY subquery2.Make,subquery2.Model, subquery2.Color,subquery2.Type,subquery1.cpp UNION SELECT Printers.Make, Printers.Model, Consumables.Color, Consumables.Type, min(Cost/Yield) AS cpp,min(Cost/Yield) AS cpp2 FROM Printers JOIN PrintersAndConsumables ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN Consumables ON Consumables.ConsumableID = PrintersAndConsumables.ConsumableID WHERE Consumables.Cost Is Not Null AND Consumables.Yield Is Not Null GROUP BY Printers.Make, Printers.Model, Consumables.Color, Consumables.Type HAVING COUNT(*)=1 ORDER BY Make, Model; Can this be the results we're after ? Best, Oliver Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql