DEV Community

loading...
Cover image for SQL Tip - Weird Behaviour of SUM or any operator When joining more than 2 tables

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

jiteshdhamaniya profile image Jitesh Dhamaniya ・1 min read

When joining more than two tables and you want to some operator thing such as SUM for instance from one table column, make sure joining table 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
Enter fullscreen mode Exit fullscreen mode

if i don't add

channels.id = (select id from channels where channels.user_id = users.id limit 0,1) 
Enter fullscreen mode Exit fullscreen mode

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).

Discussion

pic
Editor guide
Collapse
snorkypie profile image
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 :-)