I want to find groups which doesn't participate at an event.
There is my table:
groupe :
+-----------+-------------+-----------+-------------+
| id_groupe | id_createur | name | description |
+-----------+-------------+-----------+-------------+
| 1 | 5 | tagada | epic |
+-----------+-------------+-------------------------+
| 2 | 1 | banana | good |
+-----------+-------------+-----------+-------------+
| 3 | 1 | blueberry | legendary |
+-----------+-------------+-----------+-------------+
| 4 | 2 | coco | great |
+-----------+-------------+-----------+-------------+
The group 1 was create by member 5, his name is 'tagada' and his description is 'epic'.
event :
+-----+-------------+----------------+
| id | name | description |
+-----+-------------+----------------+
| 1 | lunch |for lunch monday|
+-----+-------------+----------------+
| 2 | game | play videogame |
+-----+-------------+----------------+
The name of the event 1 is "lunch" and his decription is "for lunch monday".
groupe_events :
+--------------+--------------+
| id_groupe_ge | id_events_ge |
+--------------+--------------+
| 1 | 2 |
+--------------+--------------+
| 2 | 2 |
+--------------+--------------+
| 3 | 2 |
+--------------+--------------+
| 1 | 1 |
+--------------+--------------+
| 3 | 1 |
+--------------+--------------+
For example, groupe 1, 2 and 3 will participate to the events 2 AND groupe 1 and 4 will participate to the events 1
If I use LEFT JOIN, I can't specified the id of the event (id_groupe_ge)
Mysql
SELECT *
FROM groupe
LEFT JOIN groupe_events ON id_groupe_ge = id_groupe
WHERE id_groupe_ge IS NULL
OR Mysql
SELECT *
FROM groupe
LEFT JOIN groupe_events ON id_groupe_ge = id_groupe
WHERE id_events_ge IS NULL OR id_events_ge <> 2
With this method, i have also the rows of the id_events_ge 1
If I use classique JOIN and WHERE, I have groups which participate and this exactly the opposite
Mysql
SELECT *
FROM groupe
JOIN groupe_events ON id_groupe_ge = id_groupe
WHERE id_events_ge = 2
How can I find groups which doesn't participate at the event 2 for example? Here this one :
+-----------+-------------+-----------+-------------+
| 4 | 2 | coco | great |
+-----------+-------------+-----------+-------------+
Other example, i want find groups which doesn't participate at the event 1 for example? Here this one :
+-----------+-------------+-----------+-------------+
| 2 | 1 | banana | good |
+-----------+-------------+-----------+-------------+
| 4 | 2 | coco | great |
+-----------+-------------+-----------+-------------+
To find the groups that don't participate in a specific event, you need to include that condition in your JOIN, and then check for a resultant NULL value in the groupe_events table. For example, to find groups not participating in event 1:
SELECT g.*
FROM groupe g
LEFT JOIN groupe_events ge ON ge.id_groupe_ge = g.id_groupe AND ge.id_events_ge = 1
WHERE ge.id_groupe_ge IS NULL
Output:
id_groupe id_createur name description
2 1 banana good
4 2 coco great
And for event 2:
SELECT g.*
FROM groupe g
LEFT JOIN groupe_events ge ON ge.id_groupe_ge = g.id_groupe AND ge.id_events_ge = 2
WHERE ge.id_groupe_ge IS NULL
Output:
id_groupe id_createur name description
4 2 coco great