Joining on a subquery

Pretty awesome SQL trick I just learned from Wouter. In SQL you can join on a subquery and then you can even select columns from it! The cool thing is that you can do transformations such as Group By in this subquery. This enables things I could not do before such as multiple count columns in one query.

For example:

SELECT account.IDENTIFIER, subscriptionCount.subscriptions, promotionCount.promotions
FROM tomtomtbaccount account
JOIN (SELECT account_fk, COUNT(*) AS subscriptions FROM tomtomtbsubscription GROUP BY account_fk) subscriptionCount ON account.account_id = subscriptionCount.account_fk
JOIN (SELECT account_fk, COUNT(*) AS promotions FROM tomtomtbpromotion GROUP BY account_fk) promotionCount ON account.account_id = promotionCount.account_fk
ORDER BY subscriptionCount.subscriptions
LIMIT 100;

(this query is a bit heavy though...)

Also a nice memonic Wouter provided:
if you want results side by side: JOIN
if you want results in a list: UNION