mysql INNER JOIN

I am using following query to get report from different table of my database, check following...

SELECT s.id, s.name, c.name AS course_name,
s.open_bal AS open_balance, sum(i.amount) AS gross_fee,
sum(i.discount) AS discount, sum(i.amount) - sum(i.discount) AS net_payable,
SUM(r.reg_fee+r.tut_fee+r.other_fee) AS net_recieved,
(sum(i.amount) - sum(i.discount)) - SUM(r.reg_fee+r.tut_fee+r.other_fee) AS balance_due
FROM students s
INNER JOIN courses c on c.id = s.course_id
LEFT JOIN invoices i on i.student_id = s.id
LEFT JOIN recipts r on r.student_id = s.id;

Invoices

| id | student_id | amount   | discount |  dnt        | 
+----+------------+----------+----------+-------------+
| 2  | 22         | 35000    | 0        |  2011/01/01 |
+----+------------+---------开发者_如何学Python-+----------+-------------+

Not getting correct value from gross_fee and net_payable.

thanks.

Given the SUMs in the select I suppose that GROUP BY s.id should do the trick. Anyway a GROUP BY seems to be missing :)

SELECT s.id, s.name, c.name AS course_name,
s.open_bal AS open_balance,
SUM(r.reg_fee+r.tut_fee+r.other_fee) AS net_recieved,
 (sum(i.amount) - sum(i.discount)) - SUM(r.reg_fee+r.tut_fee+r.other_fee) AS balance_due
FROM students s
INNER JOIN courses c on c.id = s.course_id
LEFT JOIN invoices i on i.student_id = s.id
LEFT JOIN recipts r on r.student_id = s.id
GROUP BY s.id;

EDIT

Separate query allowing to retrieve gross_fee and net_payable for all invoices

SELECT sum(amount) AS gross_fee,
  sum(discount) AS discount,
  sum(amount) - sum(discount) AS net_payable,
FROM invoices;

Have both student records got a value for s.course_id?

You've got an Inner Join from students to courses...

INNER JOIN courses c on c.id = s.course_id

If s.course_id is null in one of the student records, the query will only return one row.