CODEWITHSUNDEEP
mysqlsql
Damiano Fontana
Damiano Fontana

Reputation: 19

MySQL SELECT records NOT EXISTS from specific date

I have two tables:

USERS

1. id
2. name
3. surname
4. email

TICKETS

1. id
2. id_user
3. film
4. date_selling

I would like to list all the users haven't bought a ticket since a specific date.

I've tried with a lot of combinations, but I can't find the right way to reach the result.

Query 1:

SELECT u.* 
FROM users u 
LEFT JOIN tickets t ON t.id_user = u.id
WHERE t.date_selling >= "2019-01-01" 
GROUP BY u.id HAVING COUNT(t.id) = 0;

Query 2:

SELECT u.* 
FROM users u 
WHERE (NOT EXISTS(
SELECT 1 
FROM tickets t 
WHERE t.id_user = u.id AND t.date_selling >= "2019-01-01"));

Upvotes: 0

Views: 1432

Answers (1)

forpas
forpas

Reputation: 164204

You need a WHERE clause:

SELECT u.* FROM users u 
WHERE NOT EXISTS(
  SELECT 1 FROM tickets t 
  WHERE t.id_user = u.id AND t.date_selling >= '2019-01-01'
);

or with a LEFT JOIN:

SELECT u.* 
FROM users u LEFT JOIN tickets t 
ON t.id_user = u.id AND t.date_selling >= '2019-01-01' 
WHERE t.id_user IS NULL;

Upvotes: 1

Related Questions