Selecting fields from one table based on unrelated fields from another table (Bridge Table with id values links them)

Question

I have 3 tables: Student, Course, and StudentCourse, which has id pairs from the other tables to show which students are taking what courses.

I'm trying to display a list of students enrolled in a course, but I'm not sure how to convey that in a single SQL statement.

The code below is just meant to show what I want to do, split into 3 statements.

int cId = "select id from course where name=someName";

int sId = "select studentId from StudentCourse where courseId=" + cId;

final statement: select name from Student where studentId=" + sId;

Sorry for the noob question, I did actually try searching for a solution, but couldn't quite find what I was looking for.

gmiley gmiley · Accepted Answer · 2016-05-30T20:47:41

can do this with JOIN clauses. Here is an example, but it would be of help to provide tha actual definition of your tables and any queries you have attempted so far, along with some sample table data and a sample of what you are expecting for output...

select s.Name
from Student s
join StudentCourse sc
on sc.StudentId = s.StudentId
join Course c
on c.CourseId = sc.CourseId
where c.CourseId = 123;

This is the basics of using a JOIN clause in your query. Again though, it would help if we had the actual table structures and some sample data.

If you have the CourseId, then you don't really need to join the Course table, if the CourseId is included in the StudentCourse table, but if you need to select using something else located only within the Course table aside from the Primary Key (CourseId), then you will need to join to it.

Selecting fields from one table based on unrelated fields from another table (Bridge Table with id values links them)

1 Answers