CODEWITHSUNDEEP
mysqlsql
Luis
Luis

Reputation: 1465

How to count rows that aren't match between two tables in MySQL?

I have the following tables:

Users:

ID    LastPaymentDate
1     2017-01-01
2     2018-02-05
3     2018-04-06
5     NULL

ActivityLog:

ID    ActivityDate
1     2017-01-01
1     2017-05-17
3     2018-05-20

I need to find out the number of users that have LastPaymentDate but doesn't have matched ActivityDate

The output result for the above data is: 2 (UserID 3 and 2).

How can I do this?

Upvotes: 1

Views: 1112

Answers (3)

Shawn DeWolfe
Shawn DeWolfe

Reputation: 111

I had a problem similar to this. My resolution was to create views to display the fields and the count as columns. Then I did a join between the views to display the net results:

CREATE ALGORITHM=UNDEFINED DEFINER=MySQL CURRENT_USER() SQL SECURITY DEFINER VIEW `subcr_count_x`  AS SELECT `x_subscriptions`.`user_id` AS `user_id`, count(0) AS `cnt` FROM `x_subscriptions` WHERE (`x_subscriptions`.`user_id` > 0) GROUP BY `x_subscriptions`.`user_id` ;

CREATE ALGORITHM=UNDEFINED DEFINER=MySQL CURRENT_USER() SQL SECURITY DEFINER VIEW `subcr_count_y`  AS SELECT `y_subscriptions`.`user_id` AS `user_id`, count(0) AS `cnt` FROM `y_subscriptions` WHERE (`y_subscriptions`.`user_id` > 0) GROUP BY `y_subscriptions`.`user_id`;

To select the records where there isn't a match, it does this.

SELECT * FROM 
`subcr_count_x` x INNER JOIN 
`subcr_count_y` y ON x.user_id = y.user_id 
WHERE x.cnt != y.cnt

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

We can try using a left join approach here:

SELECT u.ID, u.LastPaymentDate
FROM Users u
LEFT JOIN ActivityLog a
    ON u.ID = a.ID AND u.LastPaymentDate = a.ActivityDate
WHERE
    a.ID IS NULL AND u.LastPaymentDate IS NOT NULL;

enter image description here

Demo

Upvotes: 3

GMB
GMB

Reputation: 222442

Use NOT EXISTS:

SELECT COUNT(*) 
FROM Users u
WHERE 
    u.LastPaymentDate IS NOT NULL
    AND NOT EXISTS (
        SELECT 1
        FROM ActivityLog a
        WHERE u.ID  = a.ID  AND u.ActivityDate = a.ActivityDate
    )

The good thing about this approach is that it will not count several times the same record in Users, even if it has several matching record in the ActivityLog.

Upvotes: 1

Related Questions