select
   p.id as id, if(p.party_type_id =1, concat_ws(' ', p.first_name, p.last_name), p.company) as name, pt.name as type, ds.names as disciplines, left(afs.name, 40) as affiliations, concat_ws(', ', a.city, a.state) as location, p.email as email, p.website as website, date(p.created) as created
from
   parties p
   join party_types pt on p.party_type_id = pt.id
   join (
      select
         p.id as party_id, group_concat(d.name separator ', ') as names
      from
         parties p
         left join party_disciplines pd on p.id = pd.party_id
         left join disciplines d on pd.discipline_id = d.id
      group by
         p.id ) as ds on p.id = ds.party_id
   left join (
      select
         pa.party_id, group_concat(af.company separator ', ') as name
      from
         party_affiliations pa
         join parties af on pa.affiliation_id = af.id
      group by
         pa.party_id ) as afs on p.id = afs.party_id
   left join addresses a on p.id = a.party_id
order by
   name asc
limit
   100 offset 0
;

select
   count(*) as count
from
   parties p
;