Re: Lowest 2 items per - Mailing list pgsql-sql

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.ConsumableIDWHERE Consumables.Cost Is Not NullAND Consumables.Yield
IsNot NullGROUP BY Printers.Make, Printers.Model, Consumables.Color,
 
Consumables.Type) subquery1JOIN(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.ConsumableIDWHERE Consumables.Cost Is Not NullAND 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.ConsumableIDWHERE Consumables.Cost Is Not NullAND Consumables.Yield
IsNot NullGROUP BY Printers.Make, Printers.Model, Consumables.Color,
 
Consumables.Type
HAVING COUNT(*)=1ORDER 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