CODEWITHSUNDEEP
mysqlsqlwhere-clausenot-exists
Marcel
Marcel

Reputation: 49

MySQL: Find users with no submission

I'm struggling a little bit with a query and hope you can help. I have two tables. On with all the users and one with information from submitted forms. Both contain the user ID. What I would need to find out is which user from the users table does not appear on the report table.

This is what I have so far:

SELECT u.ID, u.display_name, u.user_email, r.user_id
FROM users AS u
LEFT JOIN report AS r ON u.ID = r.user_id
WHERE NOT EXISTS(
    SELECT * 
    FROM report AS rr
    WHERE u.ID = rr.user_id
)

This seems to be fine for the users who absolutely have never submitted the form. But the reports table also contains a date column and I was wondering how I can get this grouped by day. In the front end then I will hopefully have a table which shows:

date: user:
2015-01-01 user a
2015-01-01 user f
2015-01-02 user g
2015-01-02 user a
2015-01-03 user z
2015-01-03 user x

Where the users are those who have not submitted the form that day.

Hope you can help. Thank in advance!

Upvotes: 2

Views: 265

Answers (5)

bobwienholt
bobwienholt

Reputation: 17610

I'm making assumptions about column names in your report table for this answer:

SELECT x.report_date, u.user_id, u.display_name
FROM users u
JOIN (
  SELECT DISTINCT report_date
  FROM reports
) x
LEFT JOIN reports r
  ON r.user_id = u.user_id
  AND r.report_date = x.report_date
WHERE r.report_date IS NULL
ORDER BY x.report_date, u.user_id

Check out this fiddle: http://sqlfiddle.com/#!9/407ac/5

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can get the pairs of users/dates without reports. Generate all possible rows using a cross join and then filter out the ones that exist:

select u.*, r.date
from users u cross join
     (select distinct date from reports r) d left join
     reports r
     on u.id = r.user_id and d.date = r.date
where r.userid is null;

Upvotes: 0

jpw
jpw

Reputation: 44881

If you want to get a list of users that doesn't have any rows in the report table then you can generate a set that is the Cartesian product of the users and the dates that are present in the report table, and then do a left join with that set and check for null.

The Cartesian set formed by the cross join will contain all possible combinations of dates and users; that is would the report table would contain is all users had added reports on all available dates.

select r.date, u.user_id 
from report r 
cross join users u
left join (select r.date, r.user_id from users as u join report as r on u.id = r.user_id)
a on a.date = r.date and a.user_id = u.user_id
where a.date is null

Sample SQL Fiddle

With most other databases this could have been done with a set difference operator (minus or except) instead of a left join.

Upvotes: 2

Ilessa
Ilessa

Reputation: 622

Surely you could just pass in the date you wanted to check?

so something like this (using @reportDate as the parameter): 

SELECT * FROM users
LEFT OUTER JOIN report
ON users.ID = report.user_id
WHERE report.user_id IS NULL
AND report.Date = @reportDate

Upvotes: 0

Tech Savant
Tech Savant

Reputation: 3766

Left outer join with where clause...

Here is a good link ...

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

SELECT * FROM `users`
LEFT OUTER JOIN `report`
ON `users`.`ID` = `report`.`user_id`
WHERE `report`.`user_id` IS null
ORDER BY `report`.`Date`

Upvotes: 1

Related Questions