I am trying to find the the highest price model for each maker. My output should list the maker, the model and the price.
Here is my schema:
Computer Database schemas:
Product( maker, model, type)
Desktop( model, speed, ram, hd, price)
Laptop( model, speed, ram, hd, screen, price)
Printer( model, color, type, price)
Products
Maker Model Type
A 1001 desktop
A 1002 desktop
A 1003 desktop
B 1004 desktop
B 1006 desktop
B 3002 printer
B 3004 printer
C 1005 desktop
C 1007 desktop
D 1008 desktop
D 1009 desktop
D 1010 desktop
D 2001 laptop
D 2002 laptop
D 2003 laptop
D 3001 printer
D 3003 printer
E 2004 laptop
E 2008 laptop
F 2005 laptop
G 2006 laptop
G 2007 laptop
H 3005 printer
I 3006 printer
Desktop
Model Speed Ram HD Price
1001 2.5 256 80 595
1002 2.0 256 80 399
1003 3.1 512 120 899
1004 3.1 1024 120 999
1005 3.1 256 100 999
1006 4.5 512 180 1099
1007 4.5 512 200 1399
1008 4.0 512 100 1199
1009 4.5 512 120 1299
1010 3.0 256 60 495
Laptop
Model Speed Ram HD Screen price
2001 1.8 256 30 12 799
2002 2.2 128 20 14 1499
2003 2.2 512 40 14 1699
2004 2.5 256 40 12 1499
2005 2.5 512 60 15 1799
2006 2.3 256 40 15 999
2007 3.0 1024 80 17 1899
2008 2.3 256 30 14 1599
Printer
Model Color Type price
3001 True InkJet 175
3002 True InkJet 150
3003 False Laser 295
3004 False Laser 325
3005 False inkjet 80
3006 False Laser 259
This is what I have tried so far, but nothing has really been returning one model per maker that is the highest price. If anyone could help, it'd be really appreciated.
SELECT Product.Maker, Laptop.price, Laptop.Model, Desktop.model, Desktop.price
FROM `Laptop`
RIGHT JOIN Product ON Laptop.model = Product.model
LEFT JOIN Desktop ON Product.model = Desktop.Model;
