SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Exercise: 7
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fullypaglot
Starting Member

Canada
6 Posts

Posted - 08/09/2013 :  07:39:53  Show Profile  Reply with Quote
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 lapmod
from product
join
pc on product.model=pc.model
join
laptop on laptop.model=product.model
where 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

India
52317 Posts

Posted - 08/09/2013 :  07:45:56  Show Profile  Reply with Quote
i'm assuming type field will hold values as PC ,etc
so you've to do something like this
i dont understand why you want separate tables for each types as attributes seems to be similar
i would have kept them in same table with additional column as Type

select *
from Product p
inner 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
          )t
ON t.type = p.type




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

fullypaglot
Starting Member

Canada
6 Posts

Posted - 08/09/2013 :  08:28:06  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/16/2013 :  14:13:49  Show Profile  Reply with Quote
and what should be the sample output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000