Was hoping someone may be able to help me, the question I am trying is from sql-ex.ru
The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
Find the printer makers which also produce PCs with the lowest RAM and the highest-speed processor among PCs with the lowest RAM. Result set: maker.
I've tried attempting this from several different angles and have had different problems with each attempt, however in each I've been able to see why it wont work, except this one:
SELECT DISTINCT maker FROM product INNER JOIN
PC ON PC.model = product.model INNER JOIN
(SELECT p.ram, MAX(p.Speed) as speed FROM pc p
GROUP BY p.ram
HAVING p.ram =
(SELECT MIN(ram) FROM pc)
)T ON T.ram = pc.ram AND T.speed = pc.speed
It says on a testing table that it gave an extra record by 1.
As I see it the subquery should pull out [min ram][max speed] fine then by joining this into the Product table joined with the PC table only the results which satisfy the subquery should be returned?