Issue with sql join with product/printer exercices

Question

Relations:

• Product(maker, model, type)
• Laptop(Model, price, speed, ram, hd, screen)
• PC(model, price, speed, ram, hd)
• Printer(model,price, color, price)

I am now trying to find the maker of the cheapest color printer

My Query:

select maker, price from
product join printer 
on product.model=printer.model
where color='y' and price <= all(select price from printer where color='y')

the result is correct; however the error message is

Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database. * Wrong number of records (less by 2)

Answer 1

Simpler way is by using Top keyword with order by to find the printer with minimum price. This will avoid the Sub query

Join the result back with product table to find the maker

SELECT a.maker,b.price
FROM   product a
       JOIN(SELECT TOP 1 price,
                         model
            FROM   printer
            WHERE  color = 'y'
            ORDER  BY price ASC) b
         ON a.model = b.model 

Update : For Mysql use this.

SELECT a.maker,b.price
FROM   product a
       JOIN(SELECT  price,
                         model
            FROM   printer
            WHERE  color = 'y'
            ORDER  BY price ASC limit 1) b
         ON a.model = b.model 

Answer 2

This should do it for you:

SELECT Prd.Maker, Pri.Price
FROM Product Prd
INNER JOIN Printer Pri
    ON Prd.model = Pri.model
WHERE Pri.price = (
        SELECT MIN(Price)
        FROM Printer
        WHERE Color = 'Y'
        )
    AND Pri.Color = 'Y'

You'd be overcomplicating things by trying to use ALL. Just use a simple subquery to return the minimum price for a color printer.