nagasunil — 2010-10-07T06:05:34-04:00 — #1
SELECT a.fname, a.lname, p.id, p.description, p.image, p.totalamount, p.neededamount, SUM( d.amount ) AS received_amount
FROM jos_sahayata_patients AS p
LEFT JOIN jos_sahayata_address AS a ON a.type_id = p.id
LEFT JOIN jos_sahayata_donations AS d ON d.patient_id = p.id
WHERE a.type = 'p'
AND p.approve =1
GROUP BY d.amount
ORDER BY p.id DESC
LIMIT 0 , 30
nagasunil — 2010-10-07T06:32:08-04:00 — #2
My problem solved.
Thank u very much.
guido2004 — 2010-10-07T06:29:08-04:00 — #3
Don't group by d.amount, instead group by p.id
nagasunil — 2010-10-07T06:23:01-04:00 — #4
i have got 3 tables
1)jos_sahayata_patients where i have patient information
2)jos_sahayata_address where i have patient address
3)jos_sahayata_donations where donations to the patients are saved
now i want to display all the patients with their address and the sum of the donations each patients received.
if particular patient not received any amount(donations) his amount should be null.
but i am not getting all the patients records by the above query
guido2004 — 2010-10-07T06:08:43-04:00 — #5
What are you expecting, and what are you getting? Try explaining in your own words, and maybe add a data example.
Anyway, putting this
a.type = 'p'
in the WHERE clause, the LEFT JOIN of jos_sahayata_address has become an INNER JOIN. If you really need the LEFT JOIN, put that condition in the ON clause.