CODEWITHSUNDEEP
mysqljoin
Nathan Q
Nathan Q

Reputation: 1902

MySQL select where no matches in join

How would I do following in MySQL:

I have 3 tables:

user: id

communication: id, creation_date

user_communication: user_id, communication_id

Now I want to select all users that have had no communication since a given date.

Following is what I have now, but I'm stuck on how to get what I described above.

SELECT DISTINCT u.id FROM user u
LEFT JOIN user_communication uc ON uc.user_id = u.id
LEFT JOIN communication c ON c.id = uc.communication_id
WHERE c.creation_date < '2013-8-1';

Upvotes: 0

Views: 127

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The where condition is undoing the left join. The initial solution would be to move it to the on clause:

SELECT DISTINCT u.id FROM user u
LEFT JOIN user_communication uc ON uc.user_id = u.id
LEFT JOIN communication c ON c.id = uc.communication_id and c.creation_date < '2013-8-1';

But this doesn't do what you want. This retrieves all records. If you had a creation date field in the select clause, it would be NULL when there is record before that date.

For no communication since that date, you can do a "double" negative" query. Look for records that are since that date, and return the mismatches:

SELECT DISTINCT u.id
FROM user u LEFT JOIN
     user_communication uc
     ON uc.user_id = u.id LEFT JOIN
     communication c
     ON c.id = uc.communication_id and c.creation_date >= '2013-08-01'
WHERE c.creation_date is NULL;

EDIT:

I see. The problem is a little more subtle than my answer above. Each user has multiple communications, so none can be later. The following query tests this by grouping by u.id and then checking that there are no non-NULL values from the above join:

SELECT u.id
FROM user u LEFT JOIN
     user_communication uc
     ON uc.user_id = u.id LEFT JOIN
     communication c
     ON c.id = uc.communication_id and c.creation_date >= '2012-08-01'
group by u.id
having min(c.creation_date is null) = 1;

Upvotes: 3

Nathan Q
Nathan Q

Reputation: 1902

After some research and help I have following query, which seems to work:

SELECT DISTINCT(u.id)
FROM user u
WHERE (SELECT coalesce(max(c.creation_date), '1900-01-01 00:00:00') last_creation_date
       FROM user inneru 
       LEFT JOIN user_communication uc ON uc.user_id = inneru.id
       LEFT JOIN communication c ON c.id = uc.communication_id
       WHERE inneru.id = u.id) < '2012-08-01'

SQLFiddle: http://sqlfiddle.com/#!2/5dfad/10

Upvotes: 0

Taemyr
Taemyr

Reputation: 3437

SELECT DISTINCT u.id FROM user u
LEFT JOIN user_communication uc ON uc.user_id = u.id
LEFT JOIN (SELECT * FROM communication WHERE creation_date < '2013-8-1') c 
ON c.id = uc.communication_id
WHERE c.id is NULL;

Upvotes: 1

Related Questions