Photoshop Question posted in
The official user guide can be found here.

How to get the relevent table data using JOIN Query – Photoshop

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]

Tags:

3

Answers


  1. 0

    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. 0

    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. 0

    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