I am trying to show schedules for a student. This is the map out:
StudentProfile
-studentid
Registration
-registrationid
-studentid
RegistrationSchedule
-regscheduleid(is not a primary key,, is not unique,,can have a lot of instances)
-registrationdid
-scheduleid
Schedules
-schedul开发者_运维知识库eid
I wanted to show all the schedules of a student. I am really frustrated with this. Could somebody make this happen?
This can be done with the MySQL JOIN Statement
SELECT * FROM StudentProfile sp
INNER JOIN Registration r ON sp.studentid = r.studentid
INNER JOIN RegistrationSchedule rs ON r.registrationId= rs.registrationId
INNER JOIN Schedules sc ON rs.scheduleid = schedules.scheduleid
WHERE sp.studentid = DESIREDSTUDENTID
This will select all of the columns in each of the tables for the student with the id, DESIREDSTUDENTID. You can get just the data from the schedules for a student by changing the SELECT * to SELECT sc.*
It seems like the following should work, but it's only based on the information you provided (which isn't much) so there are no guarantees:
SELECT s.* from Schedules s
join RegistrationSchedule rs on s.scheduleid=rs.scheduleid
join Registration r on rs.registrationid=r.registrationid
join StudentProfile p on r.studentid=p.studentid
WHERE p.studentid=?
You'd have to pass a value for the ?
in the WHERE clause in order to get the schedule for a particular student.
select * from StudentProfile sp
inner join Registration r on r.studentid = sp.studentid
inner join RegistrationSchedule rs on r.registrationid = rs.registrationid
inner join Schedule s on s.scheduleid = rs.scheduleid
精彩评论