Win a copy of Microservices Testing (Live Project) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

query join help

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Sheriff
Posts: 22662
127
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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=
 
reply
    Bookmark Topic Watch Topic
  • New Topic