I have three tables tblCourse
, tblDegree
, tblStudent
. I have created a course and degree relation table as like tblCourseDegreeRelation
. This relational table uses foreign keys of course and degree table as like:
The tblCourseDegreeRelation
table is like:
The tblCourse
table is like:
The tblDegree
table is like:
The tblStudent
(In this table the degree id is foreign key d_id
) table is like:
I need to get all records including the tblStudent
all record using this query:
SELECT * from tbldegree d
INNER JOIN tblcoursedegreerelation cdr ON d.d_id = cdr.d_id
INNER JOIN tblcourse c ON cdr.c_id = c.c_id
INNER JOIN tblstudent s ON d.d_id = s.d_id
ORDER BY cdr.cdr_id DESC
But this only returns the one student's record while I've two students in the database see below:
How I can get all students records from the joins query?
In your case you have all inner joins, so it will return results where both/all tables satisfies their criteria (on clause).
Viewing your data your student 1 => Ali has a relation with degree 1 =>BS Information Technology. Further degree 1 has courses (1 => Programming, 2 => English, 5=> Mathematics , 6 => Electronics)
So for student 1 your inner join clause works because it has data in all joined tables.
Now if we look for your student 3 => Bilal who has a relation with degree 3 => BS Mathematics, But this degree has no assigned courses that is why your student Bilal isn't returned
To get all students no matter their related degree has courses you can turn your inner joins into left join not for all tables but for tblcoursedegreerelation
and tblcourse
SELECT *
FROM tblstudent s
INNER JOIN tbldegree d ON d.d_id = s.d_id
LEFT JOIN tblcoursedegreerelation cdr ON d.d_id = cdr.d_id
LEFT JOIN tblcourse c ON cdr.c_id = c.c_id
ORDER BY cdr.cdr_id DESC
In the result set you can see following columns as null due to no association with courses
cdr_id, c_id, d_id, c_id, c_name, c_credit