Hi, I've just started learning SQL and I have what's probably a simple question for those of you more seasoned in SQL.
I have the following tables (format NAME (list of column names)):
SUPPLIER (KEY, NAME, CITY)
CITY (NAME, STATE)
SUPPLY (SUPPLIER, PART, SHIPDATE, QUAN)
PARTS (KEY, NAME, COLOR, WEIGT, QOH) QOH=Quantity on Hand
If I want the ID-number (i.e., the KEY) of all Massachusetts-based suppliers, I can perform the following query:
SELECT key FROM supplier WHERE city IN (SELECT name FROM city WHERE state LIKE'Mass');
If I want to see the parts (and their respective quantities) delivered by the Massachusetts-based suppliers, the following query does the trick:
SELECT supplier,part,quan FROM supply WHERE supplier IN (SELECT key FROM supplier WHERE city IN (SELECT name FROM city WHERE state LIKE'Mass'));
So far so good. However, I want to change the last query so that it adds a column Weight displaying the weight for each type of part, i.e., given the part ID I need to perform a lookup in the parts-table to obtain the weight of that particular part. But how? I guess I need a third subquery in the query above but I can't get the syntax right.
Thanks for any help.