CODEWITHSUNDEEP
mysqlsql
SAVe
SAVe

Reputation: 812

join 3 tables including null values Mysql

We have three tables in MySQL. Our query must join:

1) student 
2) subject
3) mark

student 

id name class year  gender address
1 Ganesh Wagh SSC 2010 male Bhandup, Maharashtra
2 Shailesh Penkar HSC 2010 male Ghatkoper, Mumbai
3 Pooja Palshetkar BE 2014 female Vichumbe, Panvel

subject

id name
1 English
2 Hindi
3 Maths

Mark

id studentid subjectid mark
1 1 1 70
2 1 2 80
3 1 3 90
4 2 1 60
5 2 2 50
6 2 3 60

And my desired Output

Name        subject mark
Ganesh Wagh English 70
Ganesh Wagh Hindi 80
Ganesh Wagh Maths 90
Shailesh Penkar English 60
Shailesh Penkar Hindi 50
Shailesh Penkar Maths 60
Pooja Palshetkar English 0
Pooja Palshetkar Hindi 0
Pooja Palshetkar Maths 0

I try this but it's not working:

SELECT 
  student.name, 
  subject.name, 
  mark.mark 
FROM 
(mark AS mark 
RIGHT JOIN student AS student 
  ON student.id = mark.studentid) 
LEFT JOIN subject AS subject 
  ON mark.subjectid = subject.id 
ORDER BY student.id ASC

Upvotes: 2

Views: 136

Answers (2)

Legxis
Legxis

Reputation: 916

You can do it your way as well, if that is your requirement, you just need to take out the 'AS'. If you need an alias, you just write it after the table name. In this case the names are the same, so just leave it out.

SELECT 
  student.name, 
  subject.name, 
  mark.mark 
FROM ( mark
RIGHT JOIN student
  ON student.id = mark.studentid )
LEFT JOIN subject
  ON subject.id = mark.subjectid
ORDER BY student.student_id ASC;

Upvotes: 0

MikeS
MikeS

Reputation: 1764

You probably want to do a cross join so you get all subjects and all students regardless of whether or not there is a value in mark. Something like

select ...
from (student st cross join subject su)
      left join mark m on st.id = mark.studentid and su.id = mark.subjectid
order by student id asc

Upvotes: 2

Related Questions