skip to Main Content
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


  1. A couple of joins is all you need

    select su.subject_name, course_name
    from student st
    inner join subject su
      on su.course_id = st.course_id
    inner join course co
      on co.course_id = st.course_id
    where st.email = '[email protected]'
    
    Login or Signup to reply.
  2. What about the following?

    SELECT C.COURSE_NAME, SU.SUBJECT_NAME, S.EMAIL
    FROM COURSE C
    INNER JOIN SUBJECT SU ON SU.COURSE_ID = C.COURSE_ID
    INNER JOIN STUDENT S ON S.COURSE_ID = C.COURSE_ID
    WHERE S.EMAIL = '[email protected]'
    
    Login or Signup to reply.
  3. Here is the suggestion for database design about your question

    Table : courses Has many subjects

    +-----------+-------------+---------------------+
    | course_id | course_name | created             |
    +-----------+-------------+---------------------+
    |         1 | CSS         | 2017-03-20 14:21:34 |
    |         2 | HTML        | 2017-03-20 14:21:39 |
    |         3 | JS          | 2017-03-20 14:21:44 |
    |         4 | PHP         | 2017-03-20 14:21:50 |
    +-----------+-------------+---------------------+
    

    Table : students Has many courses

    +------------+--------------+------------------+---------------------+
    | student_id | student_name | email            | created             |
    +------------+--------------+------------------+---------------------+
    |          1 | Student 1    | [email protected]  | 2017-03-20 14:19:32 |
    |          2 | Student 2    | [email protected] | 2017-03-20 14:19:32 |
    +------------+--------------+------------------+---------------------+
    

    Table : subjects Belongs to courses

    +------------+-----------+--------------+---------------------+
    | subject_id | course_id | subject_name | created             |
    +------------+-----------+--------------+---------------------+
    |          1 |         1 | A            | 2017-03-20 14:20:56 |
    |          2 |         1 | B            | 2017-03-20 14:21:12 |
    |          3 |         2 | C            | 2017-03-20 14:21:22 |
    +------------+-----------+--------------+---------------------+
    

    Table : courses_students Many To Many courses students

    +----+-----------+------------+---------------------+
    | id | course_id | student_id | created             |
    +----+-----------+------------+---------------------+
    |  1 |         1 |          1 | 2017-03-20 14:44:42 |
    |  2 |         2 |          2 | 2017-03-20 14:44:42 |
    |  3 |         2 |          1 | 2017-03-20 14:44:42 |
    +----+-----------+------------+---------------------+
    

    Note : courses has many Students, students Has many courses. This is mapping table.

    MySQL Query :

    SELECT
      students.student_name,
      courses.course_name,
      subjects.subject_name
    FROM
      `students`
    LEFT JOIN
      courses_students ON students.student_id = courses_students.student_id
    LEFT JOIN
      subjects ON subjects.course_id = courses_students.course_id
    INNER JOIN
      courses ON courses.course_id = courses_students.course_id
    WHERE
      students.email = '[email protected]'
    

    MySQL Query result :

    +--------------+-------------+--------------+
    | student_name | course_name | subject_name |
    +--------------+-------------+--------------+
    | Student 1    | CSS         | A            |
    | Student 1    | CSS         | B            |
    | Student 1    | HTML        | C            |
    +--------------+-------------+--------------+
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search