开发者

Looking up fields from multiple table MYSQL

开发者 https://www.devze.com 2023-02-12 12:20 出处:网络
I am trying to show schedules for a student. This is the map out: StudentProfile -studentid Registration

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
0

精彩评论

暂无评论...
验证码 换一张
取 消