CODEWITHSUNDEEP
sqlpostgresql
Akash
Akash

Reputation: 5012

Filtering joins with conditions

I have 3 table, named as 

students (id, name)
subjects (id, name, is_published)
student_subjects (id, student_id, subject_id)

The subjects taken by a student goes in student_subjects. But there are cases where student_subjects contains NULL subject_id for a student

Here's the data for Students table

1;"John"
2;"Ahmeah"
3;"Dina"
4;"Leo"
5;"Lenon"

Subjects Table

1;"Computer Sci";1
2;"Physics";1
3;"Bio";1
4;"Maths";0

Student_subjects

1;1;1
2;1;2
3;1;4
4;2;1
5;2;3
6;2;4
7;3;2
8;4;1
9;5;NULL

Currently to fetch all the students with their subjects and also display the name of the student who has no subject attached to him, I am using the query as follows

SELECT
  students.*,
  inners.name
FROM
  students
  LEFT JOIN ( SELECT
                student_id,
                subjects.name
              FROM
              student_subjects
              JOIN subjects ON ( student_subjects.subject_id = subjects.id AND subjects.is_published = 1)
              ) AS inners ON (inners.student_id = students.id )

Is there a better way to do the same http://sqlfiddle.com/#!12/9cf93/12

Upvotes: 0

Views: 47

Answers (1)

Matt Runion
Matt Runion

Reputation: 1071

SELECT s.*, su.name AS SubjName
FROM students AS s
LEFT JOIN student_subjects AS ss ON ss.student_id = s.id
LEFT JOIN subjects AS su ON su.id = ss.subject_id

Does the same thing as the one you listed. Is that what you are after?

Upvotes: 1

Related Questions