select
  p.id,
  p.name,
  p.guild,
  coalesce(g.name, 'N/A'),
  count(distinct n.player) as nnotes,
  count(
    distinct case
      when a.lhs = p.id
      or a.rhs = p.id then a.id
    end
  ) as nassoc
from
  player p
  left join note n on n.player = p.id
  left join association a on a.lhs = p.id
  or a.rhs = p.id
  left join guild g on p.guild = g.id
group by
  p.id