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
