In MySQL, need to join two tables with one table having multiple references to the second

Question

I have two tables, one is signups and contained in it are two fields, firstchoice, and secondchoice. Another is a schedule table, and it has things like a begin and end date, and a semesterid. firstchoice and secondchoice from the signups table both reference the semesterid from the schedule table. I am trying to create a page which displays all of the registered people and the schedules they have registered for (the begin and end dates), and my current query:

$query = "SELECT * FROM signups INNER JOIN (schedule) ON signups.firstchoice=schedule.semesterid AND signups.secondchoice=schedule.semesterid";

is not returning any results from the schedule table. Is it possible to join two tables like this, with two columns on one table referencing a single column on another?

Answer 1

I think you are looking for this:

SELECT * 
FROM signups s
INNER JOIN schedule sc1 ON s.firstchoice=sc1.semesterid 
INNER JOIN schedule sc2 ON s.secondchoice=sc2.semesterid 

If they don't always have a second choice, you may want to do this:

SELECT * 
FROM signups s
INNER JOIN schedule sc1 ON s.firstchoice=sc1.semesterid 
LEFT OUTER JOIN schedule sc2 ON s.secondchoice=sc2.semesterid