Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

2 lookup tables and 1 bridge table

 
Kyupa Supa
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Kyupa Supa
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic