Hi everyone i got this db structure:
and i need to get result
every team from event_team with count of official,coach, female, male athlete
i tried query like this
SELECT DISTINCT t.*,
count(am) male_athlete,
count(af) female_athlete,
eo.official official,
eo.coach
FROM events_teams et
LEFT JOIN teams t on et.team_id = t.id
LEFT JOIN (SELECT DISTINCT officials.id offical_id,
officials.team_id team_id,
e.event_id,
count(officials) official,
count(ec) coach
FROM officials
INNER JOIN events_officials e on officials.id = e.official_id
INNER JOIN events_coaches ec on e.event_id = ec.event_id
GROUP BY officials.id, officials.team_id, e.event_id) eo
on et.event_id = eo.event_id
LEFT JOIN (SELECT athletes.id athlete_id, ea.event_id event_id, athletes.team_id team_id
FROM athletes
LEFT JOIN events_athlete ea on athletes.id = ea.athlete_id
WHERE gender = 0) am on t.id = am.team_id
LEFT JOIN (SELECT * FROM athletes WHERE gender = 1) af on t.id = af.team_id
WHERE et.event_id = '6fd7ec2c-f4e1-40b2-8ad6-57272c598a2c'
GROUP BY t.id, eo.official, eo.coach
the result nearly as i expected except the athlete count
thanks for any kind of help
