I just cannot wrap my head around a relatively simple query - although I am sure i have done similar before.
Table A contains a list of people, with some of the fields as;
guide_details.ID_Guide
guide_details.First_Name
guide_details.Last_Name
Table B is a link table for a many to many relationship, its the go between for guides table and the sessions they attend, the sessions table. Its called ‘sessions_link’
sessions_link.ID_Guide is the linking field
I want to group each person in Guides, with a count of the sessions they have attended. There will be a session_link record for each of these sessions. I suppose half of my problem is that I cannot decide which ‘type of join’ I actually want. I tried various combinations but all I get (certainly with the last example below, and most others I tried) is the total number of ALL link records alongside each name.
SELECT
guide_details.ID_Guide,
guide_details.First_Name,
guide_details.Last_Name,
Count(sessions_link.ID_Guide)
FROM
guide_details
LEFT JOIN sessions_link ON guide_details.ID_Guide = guide_details.ID_Guide
GROUP BY
guide_details.ID_Guide
Any pointers in the right direction would be appreciated.
The next problem will then be to narrow this list further to ‘between dates’ which are stored in the sessions table but i’ll come back to that another day when my headache goes away