Retrieve data from 3 tables with 2 conditions

I have 3 tables where I want to extract data with 2 conditions each for a different table. First table is a list of people's IDs and first names. Second table is a list of GroupIDs and where people are assigned to. Third is a list of EventIDs where people are signed up for.

Now I would like to get a list(Array) of CharIDs and first names of all people who are in a certain group (In this case GroupID = 15) and if they have signed up for a certain event (In this case EventID = 22) the "EventSignUp" column should show a 1 otherwise a 0.

So in this case Frank, Bart, Monica, Ralf are in group 15. But only Bart and Ralf have signed up for event 22 and get a 1 in their "EventSignUp" column. Frank and Monica get a 0.

Table Characters
CharID  Firstname   
1   Frank   
2   Linda   
3   Bart    
4   John    
5   Monica  
6   Ralf    

Table Group
GroupID CharID  
15  1   
36  4   
15  5   
15  6   
82  2   
15  3   

Table Events
EventID CharID  
11  1   
22  3   
34  5   
22  6   
87  2

So ideally, what I would like to get back would be:

CharID  Firstname   EventSignedUp           
1   Frank           0           
3   Bart            1           
5   Monica          0           
6   Ralf            1

$stmt = $db->prepare("SELECT    c.ID            AS cID,
                                c.Firstname     AS cFirstname,
                                e.EventID       AS eEventSignedUp
                            FROM Group g
                            JOIN Characters c   ON c.CharID = g.CharID
                            JOIN Events e       ON e.CharID = g.CharID AND EventID='22'
                            WHERE GroupID='15'");

Result now is:

CharID  Firstname   EventSignedUp               
3   Bart            22          
6   Ralf            22

Solution

Since you use JOIN (which defaults to INNER JOIN), you get those rows only with data in all 3 tables.

You rather need LEFT JOIN to fetch also those rows that don't satisy your event condition. Likely, you would benefit from a tutorial about the different JOIN types, for example here

A CASE expression sets the last column to 0 or 1.

Adding a ORDER BY clause enforces the sort you want.

So the whole query will be this one (see this db<>fiddle with your sample data):

SELECT
  c.CharID, 
  c.Firstname, 
  CASE WHEN e.EventID IS NULL THEN 0 ELSE 1 END AS eEventSignedUp 
FROM 
  "Group" g 
  JOIN "Characters" c   
    ON c.CharID = g.CharID 
  LEFT JOIN Events e
    ON  e.CharID = g.CharID 
    AND EventID = 22
WHERE g.GroupID = 15
ORDER BY c.CharID;