CODEWITHSUNDEEP
mysqlsqldatabase
The Stompiest
The Stompiest

Reputation: 331

How to exclude rows when a certain condition is met in MySQL

I have two datatables. One table contains information of participants (Participants). And a table which contains all registrations for the event (Registrars).

+----+-------+
| id | name  |
+----+-------+
| 1  | Peter |
+----+-------+
| 2  | John  |
+----+-------+

+-----------+----------+
| person_id | event_id |
+-----------+----------+
| 1         | 1        |
+-----------+----------+
| 2         | 2        |
+-----------+----------+
| 2         | 1        |
+-----------+----------+

I run a MySQL query to get the information of registrars of an event. The query looks like this:

SELECT name
    FROM Participants
        INNER JOIN Registrars 
            ON Participants.id=Registrars.person_id
    WHERE 
        event_id = 1

Now I want to build a query where if the participant also registered for event 2 it will not be returned in the result of the query the reg. How can I achieve this in one query?

Upvotes: 1

Views: 1305

Answers (2)

forpas
forpas

Reputation: 164089

You can group by participant and use conditional aggregation in the HAVING clause to set the conditions:

SELECT p.id, p.name
FROM Participants p INNER JOIN Registrars  r
ON p.id = r.person_id
WHERE r.event_id IN (1, 2)
GROUP BY p.id, p.name
HAVING MAX(r.event_id = 1) = 1 
   AND MAX(r.event_id = 2) = 0

You can extend the code by adding more conditions in a similar way.

Upvotes: 1

Sergey
Sergey

Reputation: 5217

SELECT name
FROM Participants as p
    INNER JOIN Registrars as reg
        ON P.id=Reg.person_id
WHERE 
    reg.event_id = 1
 and not exists (select 1 from Registrars as strr where strr.event_id=2 and p.id=strr.person_id  )

Upvotes: 1

Related Questions