Will Smyth Will Smyth - 6 months ago 8
SQL Question

How to correctly select from two tables (SQL)

I have two tables in my database Sales and Appliances:

Sales

SaleDate EmployeeID AppID Qty
---------- ---------- ----- -----------
2010-01-01 1412 150 1
2010-01-05 3231 110 1
2010-01-03 2920 110 2
2010-01-13 1412 100 1
2010-01-25 1235 150 2
2010-01-22 1235 100 2
2010-01-12 2920 150 3
2010-01-14 3231 100 1
2010-01-15 1235 300 1
2010-01-03 2920 200 2
2010-01-31 2920 310 1
2010-01-05 1412 420 1
2010-01-15 3231 400 2


Appliances

ID AppType StoreID Cost Price
---- -------------------- ------- ------------- -------------
100 Refrigerator 22 150 250
110 Refrigerator 20 175 300
150 Television 27 225 340
200 Microwave Oven 22 120 180
300 Washer 27 200 325
310 Washer 22 280 400
400 Dryer 20 150 220
420 Dryer 22 240 360


How can I obtain this results table. (That lists the number of sales of refrigerators and for each sale, displays the total sale price as well (total sale price = qty * price).

AppID AppType Qty total sale price
----- ---------------- ----------- ----------------
110 Refrigerator 1 300
110 Refrigerator 2 600
100 Refrigerator 1 250
100 Refrigerator 2 500
100 Refrigerator 1 250





My Attempt:

SELECT AppID, AppType, Qty, (Qty * Price) as 'total sale price'
FROM Sales s, Appliances a
WHERE (AppID) IN (SELECT ID FROM Appliances WHERE AppType = 'Refrigerator')
AND (AppType) IN ('Refrigerator')


Currently produces

AppID AppType Qty total sale price
----- -------------------- ----------- ----------------
110 Refrigerator 1 250
110 Refrigerator 1 300
110 Refrigerator 2 500
110 Refrigerator 2 600
100 Refrigerator 1 250
100 Refrigerator 1 300
100 Refrigerator 2 500
100 Refrigerator 2 600
100 Refrigerator 1 250
100 Refrigerator 1 300

Answer

Do a join on sales.AppId = appliances.ID

select s.AppId, a.AppType, s.Qty, 
   a.Price * s.Qty as total
from appliances a
join sales s on s.AppId = a.ID
where a.AppType = 'Refrigerator'
Comments