dimanche 22 mars 2020

SQL Query for group participant COUNT and IF isParticipant statement

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