SQL query syntax

Databases
1

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 :slight_smile:

2

Cancel my last, the below works - good old Navicat :-). Its always the simple things

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 AND guide_details.ID_Guide = sessions_link.ID_Guide GROUP BY guide_details.ID_Guide

3

In MySQL this also should work and it’s much easier to read… (query is not tested)

SELECT guide_details.ID_Guide, guide_details.First_Name, guide_details.Last_Name, Count(sessions_link.ID_Guide) AS NumberOfSessions FROM guide_details, sessions_link WHERE guide_details.ID_Guide = sessions_link.ID_Guide GROUP BY guide_details.ID_Guide

4

Thanks Tomas, that worked fine, and is neater. Now I move onto introducing table 3 to narrow down the dates, and the head scratching starts again;

Table 3 is sessions and I need two fields;

sessions.ID_Event sessions.Event_Date

In effect I want to select all session link records between the chosen dates and then run the same count against them. The tie-up betwen tables 2 and 3 are sessions.ID_Event and sessions_link.Session_ID

I want the query now to do the same count but ‘between dates’. Any pointers in the right direction, or which type of join I might need.

Long handed,

tell me between the dates chosen how many sessions each guide has attended.

5

as far as I understood you have given STARTDATE, ENDDATE and GUIDE_ID, right?
Then I would use quite similar query (not tested!)

SELECT sessions.* FROM sessions, session_id WHERE TO_DAYS(sessions.Event_Date) > TO_DAYS(STARTDATE) AND TO_DAYS(sessions.Event_Date) < TO_DAYS(ENDDATE) AND sessions_ID_Event = sessions_link.Session_ID AND sessions_link.ID_Guide = GUIDE_ID ORDER BY sessions.Event_Date ASC

6

Thanks Tomas for taking a look, I still need to count the records per Guide.

In effect get the date from table 3 sessions by using the link to table 2, which includes the session ID, and then from this table, compare against table 1 for the guide using the link to table 2 guide_id to match the records. Table 2 is just a link table many to many between 1, guide_details and 3, sessions.

7

[quote=63829:@Paul Budd]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 AND guide_details.ID_Guide = sessions_link.ID_Guide
GROUP BY
guide_details.ID_Guide[/quote]

ok, some things to mention here:

guide_details.ID_Guide = guide_details.ID_Guide AND

this exactly does nothing

LEFT JOIN

you’ll get all guides, not only the ones that do have any link to a session. But as Count(NULL) is defined as 0 that works just fine. Note that this is not equivalent to the filtered cross join Tomas has posted.
Neater could be:

LEFT JOIN sessions_link USING (ID_Guide)[/code] or even [code]NATURAL LEFT JOIN session_link

guide_details.ID_Guide, guide_details.First_Name, guide_details.Last_Name, Count(sessions_link.ID_Guide) […] GROUP BY guide_details.ID_Guide

this can / does only work as your DBMS is able to recognize ID_Guide is the PK of guide_details and thus Fist_Name, Last_Name is well defined even w/o an aggregate nor group by.

The final query could look like:

SELECT ID_Guide, First_Name, Last_Name, Count(*) AS Sessions_Attended FROM guide_details LEFT OUTER JOIN sessions_link USING(ID_Guide) LEFT OUTER JOIN sessions USING(ID_Session) WHERE Date_Session BETWEEN <date_from> AND <date_to> GROUP BY ID_Guide

I’m assuming the schema of session_link as having columns ID_Guide and ID_Session and the one of sessions to contain ID_Session and Date_Session here. Untested :slight_smile:

8

Thanks fort the response, I will have a play.