SQL Tip - Weird Behaviour of SUM or any operator When joining more than 2 tables

When joining more than two tables and you want to do some operator thing such as SUM from one table column, you have to make sure joining table query return only one result, else you would find some weird behaviour such as concat instead of sum.

Have a look on this query

select sum(payments.amount), users.id, channels.channel_name , channels.channel_id from users
left join payments on payments.user_id = users.id 
left join channels on channels.user_id = users.id and channels.id = (select id from channels where channels.user_id = users.id limit 0,1) 
group by users.id

if i don't add

channels.id = (select id from channels where channels.user_id = users.id limit 0,1) 

when joining channels table, it might fetch more than one results of channels for that user and it would make sql to behave weirdly and it ends up concating payments.amount instead of sum(payments.amount).

Comments

[Steeve Lennmark]

It's hard to help when you don't give a real example. You left join channels but don't use it so that join is unnecessary in this case. You basically fetch a random channel connected to the user in this case which is a bit weird in most cases :-)