SQL question: Need to add a column from another table
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'); 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')); Thanks for any help. |
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:
|
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; |
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\ |
All times are GMT -5. The time now is 06:25 PM. |