3 Replies - 16976 Views - Last Post: 13 August 2010 - 05:10 AM Rate Topic: -----

#1 Dean_Grobler   User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 390
  • Joined: 15-January 10

Querying multiple tables in one SQL statement?

Posted 13 August 2010 - 01:16 AM

Hi guys,

I have no previous experience with SQL and mostly work with java. Now I've come accross a point where I have to use SQL queries in my java app. I've found that working with one resultset at a time makes things quite difficult. Especialy if you can only query one table at a time in one result set... So the question is:

Is it possible to query multiple tables in one SQL statement/resultset?

Sure hope it's possible.
Thanks for taking a look :crazy:

Is This A Good Question/Topic? 0
  • +

Replies To: Querying multiple tables in one SQL statement?

#2 andy_pleasants   User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: Querying multiple tables in one SQL statement?

Posted 13 August 2010 - 02:36 AM

Basically yes, depending on what you are actually hoping to achieve and what your table structure is really
Was This Post Helpful? 0
  • +
  • -

#3 Dean_Grobler   User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 390
  • Joined: 15-January 10

Re: Querying multiple tables in one SQL statement?

Posted 13 August 2010 - 03:04 AM

View Postandy_pleasants, on 13 August 2010 - 01:36 AM, said:

Basically yes, depending on what you are actually hoping to achieve and what your table structure is really


These are my tables and what they contain:

STOCKS TABLE:
symbol
symbolname

USERSTOCKS TABLE:
userID
symbol

USERS TABLE:
userID
firstName
lastname

I want to produce output like this:

userID firstName lastName
symbol symbolName
userID firstName lastName
symbol symbolName
userID firstName lastName
symbol symbolName

This is probly going to be stupid, but if I could do this:

ResultSet rs = stmt.executeQuery("SELECT * FROM Users;
SELECT symbol FROM userStocks WHERE userID = userID;
SELECT symbolName FROM Stocks WHERE symbol = symbol");

while(rs.next()){
	
	userID = rs.getString("userID");
	firstName = rs.getString("firstName");
	lastName = rs.getString("lastName");
	symbol = rs.getString("symbol");
	symbolName = rs.getString("symbolName");	
		
	System.out.println(userID +"\t"+ firstName +"\t"+ lastName);
	System.out.println("\t" + symbol + "\t" + symbolName);
}



....I'd be a happy man.

Does this make sense? It's quite difficult to explain. Please ask me for ANYTHING else you might need! :clap:
Was This Post Helpful? 0
  • +
  • -

#4 andy_pleasants   User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: Querying multiple tables in one SQL statement?

Posted 13 August 2010 - 05:10 AM

OK, this is a standard Join task. I don't know if you know that much about database theory or not so I won't patronise you with the explanation but in case you don't understand My linkHere's a link.

So what you are trying to do is simply associate a record in the User table with the record in the Stock table, and the UserStock table is the table using to encode the many to many relationship. So the join would go something like this:

SELECT Users.UserID, User.FirstName, User.LastName, Symbol.symbol, Symbol.symbolname
FROM Users
INNER JOIN UserStock ON Users.UserID = UserStock.UserID
INNER JOIN Stock ON Stock.symbol = UserStock.symbol



This should match all users with stock - is this what you are trying to do?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1