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 |
fullypaglot
Starting Member
6 Posts |
Posted - 2013-08-09 : 07:39:53
|
Find out the models and prices for all the products (of any type) produced by maker B.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)select product.type, pc.price as pcprice, laptop.price lapprice, pc.model as pcmod, laptop.model as lapmodfrom productjoinpc on product.model=pc.modeljoinlaptop on laptop.model=product.modelwhere maker = 'B'the syntex runs but its not displaying any results + I know that I have some extra columns there but its for some thing else I was trying |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-09 : 07:45:56
|
i'm assuming type field will hold values as PC ,etcso you've to do something like thisi dont understand why you want separate tables for each types as attributes seems to be similari would have kept them in same table with additional column as Typeselect *from Product pinner join (select model,price,'PC' AS type from PC UNION ALL select model,price,'Laptop' AS type from Laptop union all select model,price,'Printer' AS type from Printer )tON t.type = p.type ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
fullypaglot
Starting Member
6 Posts |
Posted - 2013-08-09 : 08:28:06
|
i tried it worked but I am getting strange result. I need some time to analyze how did you wrote it but thank you very much for your time and efforts. Maybe I can tweek it a little bit if I can understand whats going on here, but I would like to thank you again for every thing.maker model type model price type A 1232 PC 1121 850.0000 PC A 1232 PC 1121 850.0000 PC A 1232 PC 1121 850.0000 PC A 1232 PC 1232 350.0000 PC A 1232 PC 1232 350.0000 PC A 1232 PC 1232 400.0000 PC A 1232 PC 1232 600.0000 PC A 1232 PC 1233 600.0000 PC A 1232 PC 1233 950.0000 PC A 1232 PC 1233 970.0000 PC A 1232 PC 1233 980.0000 PC A 1232 PC 1260 350.0000 PC A 1233 PC 1121 850.0000 PC A 1233 PC 1121 850.0000 PC A 1233 PC 1121 850.0000 PC A 1233 PC 1232 350.0000 PC A 1233 PC 1232 350.0000 PC A 1233 PC 1232 400.0000 PC A 1233 PC 1232 600.0000 PC A 1233 PC 1233 600.0000 PC A 1233 PC 1233 950.0000 PC A 1233 PC 1233 970.0000 PC A 1233 PC 1233 980.0000 PC A 1233 PC 1260 350.0000 PC A 1276 Printer 1276 400.0000 Printer A 1276 Printer 1288 400.0000 Printer A 1276 Printer 1401 150.0000 Printer A 1276 Printer 1408 270.0000 Printer A 1276 Printer 1433 270.0000 Printer A 1276 Printer 1434 290.0000 Printer A 1298 Laptop 1298 1050.0000 Laptop A 1298 Laptop 1298 700.0000 Laptop A 1298 Laptop 1298 950.0000 Laptop A 1298 Laptop 1321 970.0000 Laptop A 1298 Laptop 1750 1200.0000 Laptop A 1298 Laptop 1752 1150.0000 Laptop A 1401 Printer 1276 400.0000 Printer A 1401 Printer 1288 400.0000 Printer A 1401 Printer 1401 150.0000 Printer A 1401 Printer 1408 270.0000 Printer A 1401 Printer 1433 270.0000 Printer A 1401 Printer 1434 290.0000 Printer A 1408 Printer 1276 400.0000 Printer A 1408 Printer 1288 400.0000 Printer A 1408 Printer 1401 150.0000 Printer A 1408 Printer 1408 270.0000 Printer A 1408 Printer 1433 270.0000 Printer A 1408 Printer 1434 290.0000 Printer A 1752 Laptop 1298 1050.0000 Laptop A 1752 Laptop 1298 700.0000 Laptop A 1752 Laptop 1298 950.0000 Laptop A 1752 Laptop 1321 970.0000 Laptop A 1752 Laptop 1750 1200.0000 Laptop A 1752 Laptop 1752 1150.0000 Laptop B 1121 PC 1121 850.0000 PC B 1121 PC 1121 850.0000 PC B 1121 PC 1121 850.0000 PC B 1121 PC 1232 350.0000 PC B 1121 PC 1232 350.0000 PC B 1121 PC 1232 400.0000 PC B 1121 PC 1232 600.0000 PC B 1121 PC 1233 600.0000 PC B 1121 PC 1233 950.0000 PC B 1121 PC 1233 970.0000 PC B 1121 PC 1233 980.0000 PC B 1121 PC 1260 350.0000 PC B 1750 Laptop 1298 1050.0000 Laptop B 1750 Laptop 1298 700.0000 Laptop B 1750 Laptop 1298 950.0000 Laptop B 1750 Laptop 1321 970.0000 Laptop B 1750 Laptop 1750 1200.0000 Laptop B 1750 Laptop 1752 1150.0000 Laptop C 1321 Laptop 1298 1050.0000 Laptop C 1321 Laptop 1298 700.0000 Laptop C 1321 Laptop 1298 950.0000 Laptop C 1321 Laptop 1321 970.0000 Laptop C 1321 Laptop 1750 1200.0000 Laptop C 1321 Laptop 1752 1150.0000 Laptop D 1288 Printer 1276 400.0000 Printer D 1288 Printer 1288 400.0000 Printer D 1288 Printer 1401 150.0000 Printer D 1288 Printer 1408 270.0000 Printer D 1288 Printer 1433 270.0000 Printer D 1288 Printer 1434 290.0000 Printer D 1433 Printer 1276 400.0000 Printer D 1433 Printer 1288 400.0000 Printer D 1433 Printer 1401 150.0000 Printer D 1433 Printer 1408 270.0000 Printer D 1433 Printer 1433 270.0000 Printer D 1433 Printer 1434 290.0000 Printer E 1260 PC 1121 850.0000 PC E 1260 PC 1121 850.0000 PC E 1260 PC 1121 850.0000 PC E 1260 PC 1232 350.0000 PC E 1260 PC 1232 350.0000 PC E 1260 PC 1232 400.0000 PC E 1260 PC 1232 600.0000 PC E 1260 PC 1233 600.0000 PC E 1260 PC 1233 950.0000 PC E 1260 PC 1233 970.0000 PC E 1260 PC 1233 980.0000 PC E 1260 PC 1260 350.0000 PC E 1434 Printer 1276 400.0000 Printer E 1434 Printer 1288 400.0000 Printer E 1434 Printer 1401 150.0000 Printer E 1434 Printer 1408 270.0000 Printer E 1434 Printer 1433 270.0000 Printer E 1434 Printer 1434 290.0000 Printer E 2112 PC 1121 850.0000 PC E 2112 PC 1121 850.0000 PC E 2112 PC 1121 850.0000 PC E 2112 PC 1232 350.0000 PC E 2112 PC 1232 350.0000 PC E 2112 PC 1232 400.0000 PC E 2112 PC 1232 600.0000 PC E 2112 PC 1233 600.0000 PC E 2112 PC 1233 950.0000 PC E 2112 PC 1233 970.0000 PC E 2112 PC 1233 980.0000 PC E 2112 PC 1260 350.0000 PC E 2113 PC 1121 850.0000 PC E 2113 PC 1121 850.0000 PC E 2113 PC 1121 850.0000 PC E 2113 PC 1232 350.0000 PC E 2113 PC 1232 350.0000 PC E 2113 PC 1232 400.0000 PC E 2113 PC 1232 600.0000 PC E 2113 PC 1233 600.0000 PC E 2113 PC 1233 950.0000 PC E 2113 PC 1233 970.0000 PC E 2113 PC 1233 980.0000 PC E 2113 PC 1260 350.0000 PC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-16 : 14:13:49
|
and what should be the sample output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|