Assist with Query please

hi Everyone

My data ....

Employee_number, Segment, Business Unit, system_shortname and Auth_type
(employee_number, Segment where they work, Unit where they work, System they tried to access and authorisation method they used)

  1. The innermost query - I need to find distinct emp_number, system_short and Auth_type
    Response_code = 0. (this means an employee had an unsuccessful logon to the system and auth_type)

Then, for that innermost query I am looking for the same combination that DONT have any records where Response_code = 1. (Response_code 1 = successful access. )

Lastly I need to join that to the HR_table

My query currently looks like this, but I am not getting it right...In essence I want to find the guys that have at least ONE unsucessfull logon and have NEVER had a Successful access).

Please may you assist.

Thank you (PS I am using MySql)

Select upper(concat(hr.emp_number,'-',first_name,' ',surname)) as EMP_FULL,
From totus_hr.hr_ucn_staff_list hr
Inner join
select a.emp_number, a.system_short,a.auth_type
from totus_ods.groot_2fa_log a
inner join
select emp_number,system_short,auth_type
select emp_number, system_short,auth_type
from totus_ods.groot_2fa_log
group by 1,2,3
) x
group by 1,2,3
having count(*) = 1
) b on a.emp_number = b.emp_number and a.system_short = b.system_short and a.auth_type = b.auth_type
where response_code = 0
group by 1,2,3
) b on hr.emp_number = b.emp_number

Can you provide DDL, sample data and expected results? I'm guessing here, but does this return what you are looking for?

declare @t table(
emp_number int, 
Segment int, 
BusinessUnit int, 
system_short varchar(10) ,
Auth_type varchar(10),
ResponseCode int

declare @e table
emp_number int,
FirstName varchar(10),
LastName varchar(10)


insert into @e values

insert into @t values
(1, 1, 1, 'Short1', 'Auth1', 0),
(1, 1, 1, 'Short1', 'Auth1', 0),
(2, 2, 2, 'Short2', 'Auth2', 1)

select e.*
  from @e e
	left join (
				select Emp_Number, 
					   sum(case when ResponseCode = 0 then 1 else 0 end) UnsuccessfulLogins,
					   sum(case when ResponseCode = 1 then 1 else 0 end) SuccessfulLogins
				  from @t
				group by emp_number) t
		on e.emp_number = t.emp_number
	where (t.UnsuccessfulLogins > 0 and t.SuccessfulLogins = 0)
	or t.emp_number is null

Hello thank you Mike,

No sure what DDL is?

Data Definition Language. Basically scripts to create sample tables, then data to populate them and what your expected results are

Hi Mike,

I'm actually doing this from qlikview so I dont really hace DDL to show

I actually think that your solution might work, except that I need to join on emp_ number and system and auth_ type, where you only joined on emp_ number

Reason for that is I want to see these exceptions for every combination of these three cilumns

So if emp_number 123, auth1, system 1, has response_code 0 and no response_code 1, I want to see him


If same emp has same combination of response codes as above for auth2, system 1 and ALSO want to see him again.

Hope that helps