I have a problem with a SQL query and I am not able to solve it.
The initial situation is as follows: I have a table with users and a table with groups. Both tables are connected by a many through (group_participant) table.
The following query works:
SELECT
id,
COUNT(participant.groupId) AS participantsCount
FROM
group
LEFT JOIN
group_participant participant
ON
participant.groupId = id
GROUP BY
id
Now I want to extend this query with a filter that only returns the entries in which I am a participant, but the counting of participants should not be affected.
Afterwards it should be possible to specify a flag in the Select if the user is a participant or not
It should be something like:
SELECT
id,
COUNT(participant.groupId) AS participantsCount,
IF(participant.userId = "87b67168-0d7d-46c5-9437-29f996b3d85d", 1, 0) AS isParticipant
FROM
group
LEFT JOIN
group_participant participant
ON
participant.groupId = id
WHERE
participant.userId = "87b67168-0d7d-46c5-9437-29f996b3d85d"
GROUP BY
id
How can I solve this?
Thanks for you help and stay healthy!
Aucun commentaire:
Enregistrer un commentaire