course{
course_id,
course_name,
}
subject{
subject_id,
subject_name,
course_id
}
student{
email,
course_id,
}
This is my code , It display all the subjects belongs to the course
$_GET['email_address']=$_SESSION['email_address'];
$sql="(SELECT c.course_name course_name,su.subject_name subject_name
from subject su LEFT JOIN course c ON c.course_id=su.course_id)
UNION (SELECT c.course_name course_name,s.email_address email_address
from student s LEFT JOIN course c ON s.course_id=c.course_id
WHERE s.email_address='".$_SESSION['email_address']."')";
When the course is done by “[email protected]”, I want to get the all subjects belongs to the course which was done by “[email protected]”, [email protected] is student email
The sample data are:
course:
course_id course_name
1 IT
2 Business
3 Design
subject:
subject_id subject_name course_id
111 html 1
222 java 1
333 Econ 2
444 Photoshop 3
student:
email course_id
[email protected] 1
Then I want to get data which subjects belongs to course_id=1 do by [email protected]
3
Answers
A couple of joins is all you need
What about the following?
Table :
courses
Has manysubjects
Table :
students
Has manycourses
Table :
subjects
Belongs tocourses
Table :
courses_students
Many To Manycourses
students
MySQL Query :
MySQL Query result :