0

I have two tables:

1) STUDENTS

ID,  NAME,   LASTNAME,  CLASS, ...and more columns (not important)...
1,   Peter,  Fish,      4A
2,   Johnny, Rock,      4A
3,   Tony,   Martin,    4B
4,   David,  Blur,      4C    
5,   Joe,    Black,     4B

2) MATH_CLASS

STUDENT_ID,  POINTS    
1,           15
1,           20  
2,           30    
3,           11
3,            5
4,            3

Now, with

"SELECT ID, NAME, LASTNAME, SUM(POINTS) FROM STUDENTS, MATH_CLASS WHERE ID = STUDENT_ID GROUP BY ID"

i can get simple list of students followed by sum of the points they have at MATH_CLASS.

What I need is to get the list that contains even the two students who have no entry in MATH_CLASS (either just id and the name with no information after it or followed by 0).

Any ideas how to solve this? Thanks!

Jan Fiala
  • 1
  • 1
  • 1
  • use a "full outer join" - which is not directly available MySQL, but this post explains a workaround: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – Michael Lihs Nov 10 '16 at 23:54

1 Answers1

2

What you need is a LEFT JOIN instead.

SELECT ID, NAME, LASTNAME, SUM(COALESCE(POINTS)) 
FROM STUDENTS LEFT JOIN MATH_CLASS ON ID = STUDENT_ID GROUP BY ID

Note the use of COALESCE to avoid Null apearing in the resultset.

BTW, I highly recommend that you use lower case table and column names.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Thanks! ...why would you recommend it? ...any specific reason? – Jan Fiala Nov 11 '16 at 13:28
  • not just because it's the standard practice but because it makes code very much more readble. – e4c5 Nov 11 '16 at 13:35
  • 1
    BTW, Since you are still new here, may i point out that the preferred way of saying 'thanks' around here is by up-voting good questions and helpful answers (once you have enough reputation to do so), and by accepting the most helpful answer to any question you ask (which also gives you a small boost to your reputation). – e4c5 Nov 11 '16 at 13:36