Getting all records from INNER JOINS of multiple tables

Question

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?

if table screenshot your are added is correct, i don't see course degree mapping for degree id 3 which 2nd student is having.. make sure your this mapping is available — hiren, Jun 04 '18 at 14:12
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jun 04 '18 at 14:20
It would be great if you could create http://sqlfiddle.com/ example with all table definitions with sample dataset and share the link with us — M Khalid Junaid, Jun 04 '18 at 14:58
@MKhalidJunaid The OP has made it quite clear that they're not interested in doing that. I doubt that gentle repetition is going to sway them :-( — Strawberry, Jun 04 '18 at 15:00
Possible duplicate of [How to select from two tables in MySQL even if not all rows in one table have corespondents in the other?](https://stackoverflow.com/q/3969452/3404097) — philipxy, Jun 19 '22 at 02:05

score 1 · Accepted Answer · answered Jun 05 '18 at 06:37

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

Demo

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

nacho · Answer 2 · 2018-06-04T14:58:28.230

-1

Just do a Right join on tblstudent:

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
RIGHT JOIN tblstudent s ON d.d_id = s.d_id
ORDER BY cdr.cdr_id DESC

EDIT This way is better:

SELECT c.d_id,c.d_name,c.d_fee,cdr.cdr_id,cdr_c_id,deg.c_name,deg.d_credit,a.s_id,a.s_name
FROM tblstudent a
     left join tblDegree c ON a.d_id = c.d_id
     left join tblcoursedegreerelation cdr ON cdr.d_id=c.d_id
     left join tblcourse deg on deg.c_id=cdr.c_id

ORDER BY cdr.cdr_id DESC

edited Jun 04 '18 at 14:58

answered Jun 04 '18 at 14:09

nacho

5,280
2
25
34

Note that nobody ever uses RIGHT JOIN – Strawberry Jun 04 '18 at 14:21
And please don't use `SELECT *` – Strawberry Jun 04 '18 at 14:28

Getting all records from INNER JOINS of multiple tables

2 Answers2