• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query join help

 
Daniel Cooper
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have three tables

Table1 == City
ID -- Name -- CountryCode -- District -- Population

Table2 == Country
Code -- Name --n[3] -- ... -- n[13] -- Captial

Table3 == CountryLanguage
CountryCode -- Language

City.CountryCode equals Country.Code
Country.Capital equals City.ID
City.CountryCode && Country.Code equals CountryLanguage.CountryCode

I'm having difficulties understanding whats going wrong with my join statements or if I'm using it right at all.

I'm trying to produce a ResultSet that allows me to replace CountryCode from City with Name from Country ect. in a webpage generated by a servlet.

I'm using SELECT * FROM City JOIN Country ON City.CountryCode=Country.Code WHERE City.Name=SomeVariableName

Am I using this correctly if so how do I access Country.Name calling rs.getString("Country.Name")? Also my result set is limited to the same 5 columns already in City.

So lost and banging my head against the keyboard doesn't seem to be producing results. Any help is greatly appreciated.

P.S. I also need to pull Language where country code matches
 
Daniel Cooper
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This forum is so awesome all it took was for me to post a question to have a solution suddenly appear in my head.

Mod Please Delete -- Resolved
 
Rob Spoor
Sheriff
Pie
Posts: 20745
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We don't delete threads because others may learn from them in the future. Speaking of which, would you like to share your solution for others?
 
Daniel Cooper
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sure the problem was that I was making an ambiguous reference in the WHERE clause. Instead of using WHERE City.Name= I was just using WHERE Name= and since both Table City and Table Country have a column called Name I wasn't getting proper results.

For some reason catalina.out was not receiving an SQL exception.

I only noticed it here when I went back to edit my original post to contain WHERE City.Name=
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic