LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 09-19-2005, 04:44 PM   #1
Hivemind
Member
 
Registered: Sep 2004
Posts: 273

Rep: Reputation: 30
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');
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.
 
Old 09-19-2005, 05:18 PM   #2
Matir
Moderator
 
Registered: Nov 2004
Location: San Jose, CA
Distribution: Ubuntu
Posts: 8,507

Rep: Reputation: 117Reputation: 117
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.
 
Old 09-19-2005, 05:34 PM   #3
Hivemind
Member
 
Registered: Sep 2004
Posts: 273

Original Poster
Rep: Reputation: 30
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.
 
Old 09-19-2005, 06:02 PM   #4
Hivemind
Member
 
Registered: Sep 2004
Posts: 273

Original Poster
Rep: Reputation: 30
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;
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL statement to get the last row in a table oulevon Programming 11 04-03-2009 08:30 PM
add id in the first column alaios Linux - General 1 11-19-2004 06:49 AM
Finding the name and data type of a column in SQL Travis86 Programming 11 08-15-2004 04:20 PM
help on finding the number that has max occurrences in a column [sql query] zeppelin Programming 6 06-15-2004 01:41 PM
add new column Eddie9 Linux - General 2 04-09-2002 12:05 PM


All times are GMT -5. The time now is 02:34 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration