By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Bridge table in mySQL

sunsolaris2000
P: 20
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**
Expand|Select|Wrap|Line Numbers
  1. prof_id | name
  2.    1      John
**professors_courses**
Expand|Select|Wrap|Line Numbers
  1. prof_id | course_id
  2.    1         2
  3.    1         3
  4.    1         5
  5.    2         1
**courses**
Expand|Select|Wrap|Line Numbers
  1. course_id | title
  2.     1      English 
  3.     2      French
  4.     3      Italian
  5.     4      Japanese
  6.     5      Polish
Given the name "John", seems he has the following courses: French,Italian,Polish
Jul 26 '12 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Join all the tables together and filter for the professor's name in the where clause.
Jul 26 '12 #2

sunsolaris2000
P: 20
Well finally I did:

Expand|Select|Wrap|Line Numbers
  1. String sqlQuery = "SELECT courses.title FROM courses INNER JOIN professors_courses ON professors_courses.course_id = course.course_id WHERE prof_id = '"+x+"'";
User will choose a professor and I find out his id and assert his id in x.

Thank you for your time anyway :)
Jul 27 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.