use joins instead of subqueries

for better mysql perf

sits on top of #28
can we move this to the module level?