CODEWITHSUNDEEP
mysqlsqljoin
jemminger
jemminger

Reputation: 5173

SQL: how to find people that have attended only one event

Given this schema and data:

create table contacts (id int, name varchar(255));
create table events (id int, name varchar(255));
create table tickets (contact_id int, event_id int);

insert into contacts (id, name) values (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
insert into events (id, name) values (1, 'Event 1'), (2, 'Event 2');
insert into tickets (contact_id, event_id) values (1,1), (1,2);
insert into tickets (contact_id, event_id) values (2,2);

How do I find the people that have attended exactly one event? I would expect to find only Bob, who has attended only one event, not Alice who has been to two events and not Charlie who has attended zero.

Potentially there could be hundreds of contacts and events, and I want to find those contacts for a particular event that have not attended any events before.

I'm drawing a blank on this one.

Edit: let me clarify,

How do I find the people that have attended exactly one specific event? For event 2, I would expect to find only Bob, who has attended only one event, not Alice who has been to two events and not Charlie who has attended zero.

Upvotes: 0

Views: 1308

Answers (2)

Cade Roux
Cade Roux

Reputation: 89651

This version may perform just as well, depending upon your optimizer, and the intent is explicit (all contacts who attended a specific event, but not any other event):

SELECT *
FROM contacts c
WHERE EXISTS (SELECT * FROM tickets WHERE contact_id = c.id AND event_id = 2)
AND NOT EXISTS (SELECT * FROM tickets WHERE contact_id = c.id AND event_id <> 2)

Upvotes: 0

Wrikken
Wrikken

Reputation: 70460

@yang's answer would work for users which have attended only one unspecific event, however, if you want to find ones attending a particular as their first and only one, this would work if tickets.event_id cannot be NULL:

SELECT t1.contact_id
FROM tickets t1
LEFT JOIN tickets t2
    ON t2.contact_id = t1.contact_id
    AND t2.event_id != t1.event_id
WHERE t1.event_id = 'someid'
    AND t2.event_id IS NULL;

A proper index on (tickets.contact_id, tickets.event_id)is assumed for performance..

Upvotes: 1

Related Questions