I want to search every company (niu_lds) that does NOT have any Analyst (cod_role_acces) working for it.

Example: Let's say there is a company X with 3 employees. One of them is an analyst. I would not want that company to come up in my results. but if there is company Y with 2 employees, and none of them is an "analyst", then I would like this company to come up in the result.

This is my query:

SELECT DISTINCT
    a.name_pers as "First Name",
    a.last_pers as "Last Name",
    a.id as "# account",
    a.cod_role_acces as "Profile", 
    a.niu_lds as "Company", 
    b.cod_missn as "Mission", 
    
FROM sr.c_r_v_obt_cp_util a inner join ods.c_od_missn_ld b on a.niu_lds = b.niu_lds

WHERE a.cod_eta_util in ('VER', 'APPR')
and a.id_cod_sr_alim = '2'
and b.cod_missn = 'PHA'

 and not exists (select null
                 from sr.c_r_v_obt_cp_util c 
                 where c.niu_lds = a.niu_lds
                 and a.cod_role_acces = 'ANALYST'
                );

My problem is it's returning me "company" that HAVE "analyst(s)"! I want to see all the company (niu_lds) that has no 'ANALYST'(cod_role_access) attributed to said company.

I already asked the question, and someone helped me a lot but I'm still not getting the expected output.

my first question : SQL query to check if a value isn't present

Thank you

CodePudding user response：

I believe the answer from Romeo with changing the null to 1 might get you there.

If it doesn't and you want to see visually what's happening to compare, you could turn the not exists function into a left join so you can see where values are getting placed.

Using the test example from the previous post -

with test (company, ename, profile) as
      (select 'BMW', 'Scott', 'Analyst' union all
       select 'BMW', 'King' , 'Manager' union all
      select 'BMW', 'Mike' , 'Clerk'  union all
      select 'SAP', 'John' , 'Clerk' union all
      select 'SAP', 'Fred' , 'Manager' 
    )
  select a.company, a.ename, a.profile, b.analyst
  from test a
  left join (select 1 as analyst, company
        from test
        where profile = 'Analyst') b
        on b.company = a.company

The following is the return -

You can see that all of BMW is getting marked for Analyst, from here, if you wanted to build off the left join rather than not exists but 'hide' the column marking Analyst, add 'where b.analyst is null' and remove b.analyst from the select statement -

with test (company, ename, profile) as
      (select 'BMW', 'Scott', 'Analyst' union all
       select 'BMW', 'King' , 'Manager' union all
      select 'BMW', 'Mike' , 'Clerk'  union all
      select 'SAP', 'John' , 'Clerk' union all
      select 'SAP', 'Fred' , 'Manager' 
    )
  select a.company, a.ename, a.profile
  from test a
  left join (select 1 as analyst, company
        from test
        where profile = 'Analyst') b
        on b.company = a.company
where b.analyst is null

This returns the expected table -

Edited to add OP's equivalents:

These should match up with the logic originally posted and your code -

With AnalystFlag present -

SELECT DISTINCT
    a.name_pers as "First Name",
    a.last_pers as "Last Name",
    a.id as "# account",
    a.cod_role_acces as "Profile", 
    a.niu_lds as "Company", 
    b.cod_missn as "Mission", 
    c.analyst as "AnalystFlag"
    
FROM sr.c_r_v_obt_cp_util a 
  inner join ods.c_od_missn_ld b 
    on a.niu_lds = b.niu_lds
  left join (select distinct 1 as analyst, niu_lds
                 from sr.c_r_v_obt_cp_util
                 where cod_role_acces = 'ANALYST') c
                 on a.niu_lds = c.niu_lds

WHERE a.cod_eta_util in ('VER', 'APPR')
and a.id_cod_sr_alim = '2'
and b.cod_missn = 'PHA'
;

With AnalystFlag removed -

SELECT DISTINCT
    a.name_pers as "First Name",
    a.last_pers as "Last Name",
    a.id as "# account",
    a.cod_role_acces as "Profile", 
    a.niu_lds as "Company", 
    b.cod_missn as "Mission", 
    
FROM sr.c_r_v_obt_cp_util a 
  inner join ods.c_od_missn_ld b 
    on a.niu_lds = b.niu_lds
  left join (select distinct 1 as analyst, niu_lds
                 from sr.c_r_v_obt_cp_util
                 where cod_role_acces = 'ANALYST') c
                 on a.niu_lds = c.niu_lds

WHERE a.cod_eta_util in ('VER', 'APPR')
and a.id_cod_sr_alim = '2'
and b.cod_missn = 'PHA'
and c.analyst IS NULL
;