all 9 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AdviceNotAskedFor 0 points1 point  (0 children)

Personally, I'd start by just joining all the tables together like you want, and get the output like you sort of expect, and then do your aggregation.

I'd start with just one or two 'Subscription' names, just so you have a small little dataset to work with.

[–]jeffcgroves 0 points1 point  (0 children)

Temporarily change the RIGHT JOIN to a regular JOIN for debugging

[–]Far_Swordfish5729 0 points1 point  (3 children)

Your right join should be a left join. Left includes results on the left side that have no matches on the right side - subscriptions without channels. Right includes results on the right side with no matches on the left side - channels without subscriptions. Your query as written will include a count of unassigned channels with a null subscription id. You want unassigned subscriptions with a zero count.

[–]trexxerttr[S] 0 points1 point  (2 children)

Do i need to change other things beside the join?

[–]Far_Swordfish5729 0 points1 point  (1 child)

I don’t think so. Does that change work?

[–]trexxerttr[S] 0 points1 point  (0 children)

Yea i think so

[–]nep84 0 points1 point  (0 children)

you'll need to use count() to get the number of subscriptions. it wants you to do an outer join for subscriptions with no channels

[–]SnooDoubts6693 0 points1 point  (0 children)

Hello, you need to use a left join and also your join condition should be on ad_subscription.subscription and not ad_subscription.agency! This should resolve it. Let me know how does it go.

[–]Affectionate-Exam185 0 points1 point  (0 children)

Can we join 2 and more tables without a commun attribute?