wood burning stoves 2.0*
The moose likes JDBC and the fly likes 2 lookup tables and 1 bridge table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "2 lookup tables and 1 bridge table" Watch "2 lookup tables and 1 bridge table" New topic
Author

2 lookup tables and 1 bridge table

Kyupa Supa
Ranch Hand

Joined: Jun 23, 2012
Posts: 30
I have 2 tables:
professors(professor_id INT PK, name VARCHAR), courses(course_id INT PK, title).
I also have a bridge table: professors_courses(prof_id INT FK, course_id INT FK).

I still didn't find a query for something like this:
given the professor name, "x"

Using the bridge table, find all the courses for professor X.

**professors**
prof_id | name
1 John

**professors_courses**
prof_id | course_id
1 2
1 3
1 5
2 1

**courses**
course_id | title
1 English
2 French
3 Italian
4 Japanese
5 Polish

Given the name "John", seems he has the following courses: French,Italian,Polish
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1774
    
  14

Kyupa Supa wrote:Given the name "John", seems he has the following courses: French,Italian,Polish

Yes, those would be the correct results based on your data. What does you query look like, and what results does it return?

It might help to refresh your memory on SQL joins to navigate from your Professors table to the Courses table.


No more Blub for me, thank you, Vicar.
Kyupa Supa
Ranch Hand

Joined: Jun 23, 2012
Posts: 30
I found out how:

String sqlQuery =

I forgot to take into account that user chooses a professor name, I look for he's ID and then I use this Query.

Thank you for your time
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1774
    
  14

Kyupa Supa wrote:String sqlQuery =


Actually, you can join more than two tables, so you could navigate from professors --> professors_courses --> courses in a single SQL statement i.e. you don't need to look up the Professor ID separately first.

Also, now you've figured out how to write a SQL join, remember that you also need to guard against SQL injection by using a PreparedStatement and not just concatenating your search parameters onto your query string as you do here.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: 2 lookup tables and 1 bridge table