By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table "Characters" (CharID int, Firstname varchar(100));
insert into "Characters" values
(1,'Frank'),
(2,'Linda'),
(3,'Bart'),
(4,'John'),
(5,'Monica'),
(6,'Ralf');
6 rows affected
create table "Group" (GroupID int, CharID int);
insert into "Group" values
(15,1),
(36,4),
(15,5),
(15,6),
(82,2),
(15,3);
6 rows affected
create table Events (EventID int, CharID int);
insert into Events values
(11,1),
(22,3),
(34,5),
(22,6),
(87,2);
5 rows affected
SELECT
c.CharID 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;
| cID | cFirstname | eEventSignedUp |
|---|---|---|
| 3 | Bart | 22 |
| 6 | Ralf | 22 |
SELECT
c.CharID AS cID,
c.Firstname AS cFirstname,
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;
| cID | cFirstname | eEventSignedUp |
|---|---|---|
| 1 | Frank | 0 |
| 3 | Bart | 1 |
| 5 | Monica | 0 |
| 6 | Ralf | 1 |