SQL question: Need to add a column from another table

Hivemind · 09-19-2005, 04:44 PM

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:

Code:

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:

Code:

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.

Matir · 09-19-2005, 05:18 PM

Or use an implicit join. Given that I don't know all the relationships in your database, here's an example:
Tables:
Customers(id,name)
Orders(id,customerid,total)

If I want to find all orders for a customer by name, I use:

Quote:

select orders.id,orders.total from orders,customers where orders.customerid=customers.id and customers.name like 'SOMENAME';

No sub-query needed.

Hivemind · 09-19-2005, 05:34 PM

Thanks for the reply, Matir. I will see if I can find a good reference on joins to help me write clearer and better code.
Anyway, I solved the problem like this for now:

Code:

SELECT supply.supplier,supply.part,supply.quan,parts.weight FROM supply,parts WHERE supplier IN (SELECT key FROM supplier WHERE city IN (SELECT name FROM city WHERE state LIKE'Mass')) AND supply.part=parts.key;

I also need to learn proper indentation rules for SQL so I can split my queries into several lines.

Hivemind · 09-19-2005, 06:02 PM

Here's a variant that doesn't use subqueries (instead it uses implicit join?):

Code:

SELECT supply.supplier,supply.part,supply.quan,parts.weight FROM supply,supplier,city,parts WHERE supply.supplier=supplier.key AND su\
pplier.city = city.name AND city.state LIKE'Mass' AND supply.part=parts.key;