Using nested SELECT result for IN statement of another nested SELECT-CodePudding

Be gentle. I'm a high school principal coding on the side for our school site.

I have looked at answers, here, here, and here. I might just not know enough to ask the right question.

We have events that have multiple sessions and there are workshops that can be associated with multiple sessions in multiple events.

I'm trying to get a csv result, later to be put into an array, for the associated sessions and events for my Workshops.

The query below works without the second nested Select statement.

In the Alt_Events statement, I need to pull the Event_IDs that are associated with the Session_IDs that are pulled from the first nested Select.

Events
ID   |   Name            |   Description   
1    |   Flex Learning   | A day of flexible learning.
2    |   Moonshot Expo   | A day to join partners to solve problems.

Event_Sessions
ID   |   Event_ID  |  Name                 | Description
1    |      1      | Morning Session       | The first session of the day. 
2    |      1      | Afternoon Session     | The afternoon session.
3    |      1      | Tutoring Session      | A chance to get help from teachers.
4    |      2      | Partner Field Trip    | The first session of the day. 
5    |      2      | Brainstorming Session | The afternoon session.
6    |      2      | Tutoring Session      | A chance to get help from teachers.

Event_Workshops
ID   |   Name        |   Description
1    | Math Tutorial | Get help from your math teachers.

Event_Workshop_Links
ID   |   Workshop_ID  | Session_ID 
1    |      1         |     3
2    |      1         |     6

Output Table: 
    ID | Name          | Description | ... | Alt_Sessions  | Alt_Events
    1  | Math Tutorial | Get help... | ... |      3,6      |    1,2

Here is my query.

SELECT 
   ws.ID, ws.Name, ws.Description, ws.Location, ws.Owner_ID, ws.Max_Attendees,  
   ws.Eng_Major_Allowed, ws.Eng_Minor_Allowed, 
   ws.HC_Major_Allowed, ws.HC_Minor_Allowed, 
   ws.IT_Major_Allowed, ws.IT_Minor_Allowed,
   u.LastName as Owner_LastName, u.FirstName AS Owner_FirstName, u.Email AS Owner_Email,
        (SELECT group_concat(SESSION_ID) FROM Events_Workshops_Links WHERE Workshop_ID = ws.ID) AS Alt_Sessions,
        (SELECT group_concat(Event_ID) FROM Event_Sessions WHERE Session_ID IN Alt_Sessions) AS Alt_Events
   FROM Event_Workshops as ws 
   LEFT JOIN users AS u
   ON ws.Owner_ID = u.ID 
   WHERE ws.ID = ? 
   ORDER BY ws.Name

I need to be able to pull the all event_ids that are in the Alt_Sessions result.

I'm guessing I can't use the result of the first nested query in the second nested query. If that's the problem, how can I pull that list of event ids?

Any and all help is greatly appreciated.

(Updated to show expected output. Also one error in transcribing the query. Session_ID instead of Event_ID in second nested statement.

CodePudding user response：

Use the subquery instead of Alt_Sessions in the IN predicate like below.

(SELECT group_concat(SESSION_ID) FROM Events_Workshops_Links WHERE Workshop_ID = ws.ID) AS Alt_Sessions,
(SELECT group_concat(Event_ID) FROM Event_Sessions WHERE Session_ID IN (SELECT SESSION_ID FROM Events_Workshops_Links WHERE Workshop_ID = ws.ID)) AS Alt_Events

Also, there is a way to make combinations of Alt_Sessions and Alt_Events first and then join to Event_Workshops.

SELECT * FROM Event_Workshops ws
JOIN
(
  SELECT
    wsl.Workshop_ID,
    GROUP_CONCAT(wsl.Session_ID) Alt_Sessions,
    GROUP_CONCAT(wsl.ID) Alt_Events
  FROM Event_Workshop_Links wsl
  GROUP BY wsl.Workshop_ID
) w
ON ws.ID = w.Workshop_ID