Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
rob887
Starting Member
2 Posts |
Posted - 2012-05-11 : 04:32:45
|
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)Exercise: 25Find 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 JOINPC 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? |
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-05-11 : 06:01:11
|
can you provide sample data for all tables? |
|
|
rob887
Starting Member
2 Posts |
Posted - 2012-05-11 : 06:11:35
|
you know what... I went to grab you the sample data and for the tables I've used and noticed what the problem is. I haven't included the fact that they only want printer manufacturers :(. Been looking at this for hours and missed the obvious!!! |
|
|
gmb.dorr
Starting Member
4 Posts |
Posted - 2013-01-22 : 17:33:35
|
I'm stuck in this :( |
|
|
|
|
|
|
|