Mysql SUM 0 when not in another table -

i have 2 tables:

users: id   |  name   |  club_id | 1       bob         4 2       jane        5 3       alex        4  4       paul        4 5       tom         4  points: user_id    |   club_id    |   amount(can vary) 1                 4             10 1                 2             10 2                 5             10 3                 4             10 3                 4             10 4                 4             10 3                 2             10 3                 4             10  need (where users.club_id = 4 , points.club_id = 4): user_id    |    name    |   sum(amount) 3             alex           30 1             bob            10 4             paul           10 5             tom             0       

notice tom present in users doesn't have entries in points, sum should 0. throws me off in conjuction grabbing list users.

also efficient possible (hence added club_id = 4 both in users , points)

try this:

select   u.id,   u.name,   coalesce(sum(p.amount), 0) totalpoints (   select *    users   club_id = 4 ) u left join (   select *    points   club_id = 4 ) p on p.user_id = u.id  group u.id,           u.name; 

see in action here:

• sql fiddle demo