Assist with Query please

1

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
    WHERE
    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,
hr.division,segment,business_unit,system_short,auth_type
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
from
(
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

2

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
(1,'First1','Last1'),
(2,'First2','Last2'),
(3,'First3','Last3'),
(4,'First4','Last4'),
(5,'First5','Last5'),
(6,'First6','Last6'),
(7,'First7','Last7')


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
3

Hello thank you Mike,

No sure what DDL is?

4

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

5

Hi Mike,

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

6

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

And

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