MySQL query missing IDs

Question

I have three tables, I want to query which people were absent (missing in the event i.e when a idu is missing in the attendance table for a certain event) from an event and how many times they were absent within a certain date range. For example if an event happens 5 times and a user goes to one of them, I want to be shown that a user missed Four events. I also would like to set the least amount of times an individual can be absent to be included in the absentee list to be 3. Therefore the query will show me users who have missed events more than 3 time. My tables are as show below

Users Table

idu fname lname 1 John Doe 2 Jane Doe 3 Mary Jane 4 John Rancho

Events Table

id_event event_name 1 Conference 2 Fellowship 3 Orientation

Events attendance table

id_attendance id_event idu event_date 1 1 1 2012-02-01 08:00:00 2 1 2 2012-02-01 08:00:00 3 2 1 2012-06-07 08:00:00 4 2 3 2012-06-07 08:00:00 5 3 1 2013-07-12 08:00:00 6 3 2 2013-07-12 08:00:00 7 1 1 2014-05-31 08:00:00 8 1 3 2014-05-31 08:00:00 9 2 1 2015-02-08 08:00:00

I would like the results to be shown as below whereby the first column is the idu Therefore the results will look like so

IDU Name Times Absent 2 Jane Doe 3 3 Mary Jane 4

My query is shown below. I'm stuck.

SELECT idu FROM users WHERE idu NOT IN ( SELECT idu FROM events LEFT JOIN attendance ON event_id=id_event WHERE event_date>='2011-04-08 00:00:00' AND event_date<='2019-04-08 00:00:00' AND id_event IN(11,10) AND idu IS NOT NULL)

Please explain what "absent" means in terms of your data (it is clearly not a field in any of the tables). Also, how to you get the values of 3 and 4 for those two users, given the data in the question? — Gordon Linoff
– Gordon Linoff, Commented Apr 8, 2015 at 18:33
You have a problem here in your schema in that you have multiple event data for a single event id. This make is unclear as to what it means to "miss" an event. If an event happens 5 times and a user goes to one of them. How many events did they miss? Did they miss 4 events? Did they not "miss" this event at all since they went at least once? — Mike Brant
– Mike Brant, Commented Apr 8, 2015 at 18:38
@Mike, I want to query when an idu is missing in the attendance table for a certain event. For example if an event happens 5 times and a user goes to one of them, I want to be shown that a user missed Four events. — sammyukavi
– sammyukavi, Commented Apr 8, 2015 at 18:41

Mogsdad · Accepted Answer · 2015-12-10 04:41:46Z

1

SELECT ut.idu as IDU, ut.fname || ut.lname as Name, (SELECT COUNT(*) FROM events_table) - SUM(CASE WHEN at.idu is null then 0 else 1 end) as TimesAbsent FROM attendance_table at RIGHT JOIN user_table ut ON at.idu = ut.idu GROUP BY ut.idu, ut.fname, ut.lname

edited Dec 10, 2015 at 4:41

Mogsdad

46k21 gold badges166 silver badges289 bronze badges

answered Apr 8, 2015 at 18:55

Marcelo

4291 gold badge6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sammyukavi Over a year ago

I need to know the times a user did not show up or a user who never showed up at all.

Marcelo Over a year ago

I fixed, now with the right join is counting the user who never showed up

sammyukavi Over a year ago

Thanks for the help. Works, now I have to know how to deal with the negatives

Z.I.J · Accepted Answer · 2015-12-10 10:38:33Z

I think, you want to get number of users that, they have more than or equal to four absents.

Please use the below query.

select * from (select u.idu as IDU,concat(u.fname,' ', u.lname) as Name, (CASE WHEN a.idu IS NULL THEN (SELECT count(distinct id_event,event_date) FROM attendance) ELSE ((SELECT count(distinct id_event,event_date) FROM attendance) - count(*)) END) as Number_of_Absent from users u left join attendance a on (a.idu=u.idu) group by concat(u.fname,' ', u.lname) order by Number_of_Absent) a where Number_of_Absent >=4 ;

You can see the query result please click.. SQL fiddle

If you want to see all users absent then you can try the below query.

select u.idu as IDU, concat(u.fname,' ', u.lname) as Name, (CASE WHEN a.idu IS NULL THEN (SELECT count(distinct id_event,event_date) FROM attendance) ELSE ((SELECT count(distinct id_event,event_date) FROM attendance) - count(*)) END) as Number_of_Absent from users u left join attendance a on (a.idu=u.idu) group by concat(u.fname,' ', u.lname) order by Number_of_Absent

You can see the query result please click.. SQL fiddle

According to the above tables data following users absent list

Collectives™ on Stack Overflow

MySQL query missing IDs

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related