-1

I am trying to pull data of employees who have NOT EVER attended a training. There is a column called "Attended" and when the employee does not attend the training, they get entered into the database a second time as "Attended = 0" but once they end up attending the training the next week they get another row added where "Attended = 1"

When I am pulling data where Attended = 0, it is pulling up data of employees that have not ever attended the training, AND those who have missed the training once but then went to it a following week.

How do I get those employees where "Attended" NEVER equals "1" but also equals 0?

Matthew
  • 83
  • 6
  • `they get entered into the database a second time as "Attended = 0"` When did they get entered the first time? Could you show the state of your table as clear rows and what you expect to get? – Schwern Jul 29 '19 at 20:35
  • sorry that was not supposed to say "a second time" there. At this point, this will be the first time they are entered in the DB – Matthew Jul 29 '19 at 20:38
  • When they do not attend, they are add to the DB as not attended, and when they do attend they are added again as a second entry to the DB as attended. Since I am trying to pull those who have not attended, I am getting the first "Attended = 0" DB entry even if they now have a second entry that says "Attended =1" So I only want to show those who never have a data entry with "Attended = 1". Does this make sense? – Matthew Jul 29 '19 at 20:47
  • Yes, I follow that part. How do you know who is supposed to attend the session in order to mark them as having not attended? How do you know that employee 1234 was supposed to have attended a training? – Schwern Jul 29 '19 at 20:50
  • Trainers look to see if the employees have Attended = 0 , and then send them an invitation to the training. But, the reports are showing them "0's" from people who have attended the training now. So people who first missed the training, then went the next time they were invited, are getting invited again. This is why I am building the report actually! – Matthew Jul 29 '19 at 20:56

2 Answers2

1

Assuming there is a column employeeid, you can group by employeeid and use aggregation to get the employees that have only attended = 0 in the table:

select employeeid
from tablename
group by employeeid
having sum(attended) = 0
forpas
  • 160,666
  • 10
  • 38
  • 76
0

You can use an except to get all those where attended = 0 except those where attended = 1. Unfortunately MySQL doesn't support except so we have to fake it.

For example...

select *
from attendance;

employee_id  training_id  attended  
-----------  -----------  ----------
1            1            0         
1            1            1         
2            1            0         
3            1            1         
1            2            0         
1            3            1

In this example employee 1 has to make up training 2, and employee 2 has to make up training 1.

select employee_id, training_id
from attendance
where attended = 1;

employee_id  training_id
-----------  -----------
1            1          
3            1          
1            3

That is everyone who attended their training.

select employee_id, training_id
from attendance where attended = 0;

employee_id  training_id
-----------  -----------
1            1          
2            1          
1            2

That's everyone who missed a training.

Normally you'd use an except here to run both queries.

select employee_id, training_id
from attendance
where attended = 0
except
select employee_id, training_id
from attendance
where attended = 1;

employee_id  training_id
-----------  -----------
1            2          
2            1

But MySQL doesn't support that. Instead we emulate it with a left excluding self join.

select a1.employee_id, a1.training_id
from attendance a1
-- Self join with everyone who attended
left join attendance a2 on
  a1.employee_id = a2.employee_id and
  a1.training_id = a2.training_id and
  a2.attended = 1
-- Get everyone who was marked as not attending and did not later attend
where a1.attended = 0
  and a2.employee_id is null;
Schwern
  • 153,029
  • 25
  • 195
  • 336
  • @Matthew Yes, it's all standard SQL. [SQL Server supports `except`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017). – Schwern Jul 30 '19 at 21:17
  • okay, for some reason this is still showing me those with 0's in Attended when they have a 1 in Attended too. I have been trying to figure out a better way to word this: so if a user_id ever has an attended = 1 in a class(which has an id too) then never display their name for that class id. does this make better since? thanks for sticking with me lol – Matthew Jul 31 '19 at 13:03
  • @Matthew As I understand you, that is what the answer is doing. If you can post a sanitized data set that demonstrates your problem I can have a look. Meanwhile [forpas's answer](https://stackoverflow.com/a/57261553/14660) is very elegant for this particular problem. – Schwern Jul 31 '19 at 21:48
  • Okay I will get one ready. But also, in forpas's answer, I am getting 'Operand data type bit is invalid for sum operator' as an error – Matthew Aug 02 '19 at 14:29
  • @Matthew I'm guessing `attended` is not an `integer` but a `bit`. Please edit your question to show us your full schema, including the types. `bit` is for binary data and not appropriate. You might be better off with a more appropriate `bool` which is a 1 byte integer. [`bit(1)` does not store 1 bit, but 1 byte](https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-numeric) so it doesn't save any storage. – Schwern Aug 02 '19 at 18:46