SQL query to check if a value isn't present-CodePudding

First of all, I really don't know how to formulate my question.

I have different companies in a database.

I would like to know which companies doesn't have an "analyst" profile.

Here is my query:

select
t.name as "name"
t.pre as "first name"
t.id as "# account"
t.profile as "Profile"
b.cod_miss as "Mission"
b.df_missn as "End date"


from sr.v t 
inner join od.e_lds on t.niu_ld = b.niu_ld
where b.cod_miss = 'APPROV'
and t.profile = 'Analyst'

This query gives me all the analyst for every companies in my database. But I would like to have all the companies that does NOT have any analyst. how do I do it? I tried using 'and t.profile <> 'analyst' " but obviously this is not working well...

EDIT:

I tried the accepted answer, but i noticed it just returns me everyone that is NOT an analyst.

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.

CodePudding user response：

If I understood you correctly, that would be not exists. Something like this:

select *
from sr.v 
where not exists (select null 
                  from od.e_lds b
                  where b.niu_ld = t.niu_ld
                    and t.profile = 'Analyst'
                 );

Applied to your query:

select
  t.name as "name"
  t.pre as "first name"
  t.id as "# account"
  t.profile as "Profile"
  b.cod_miss as "Mission"
  b.df_missn as "End date"
from sr.v t inner join od.e_lds b on t.niu_ld = b.niu_ld
where b.cod_miss = 'APPROV'
  --
  and not exists (select null 
                  from od.e_lds c
                  where c.niu_ld = t.niu_ld
                    and t.profile = 'Analyst'
                 );

[EDIT #2, with some sample data]

This is an example that shows what you tried to explain in words (would be better if you posted sample data, though). As you can see, one of employees in BMW is Analyst while nobody in SAP is >> therefore, SAP is being returned.

SQL> with test (company, ename, profile) as
  2    (select 'BMW', 'Scott', 'Analyst' from dual union all
  3     select 'BMW', 'King' , 'Manager' from dual union all
  4     select 'BMW', 'Mike' , 'Clerk'   from dual union all
  5     --
  6     select 'SAP', 'John' , 'Clerk'   from dual union all
  7     select 'SAP', 'Fred' , 'Manager' from dual
  8    )
  9  select a.company, a.ename, a.profile
 10  from test a
 11  where not exists (select null
 12                    from test b
 13                    where b.company = a.company
 14                      and b.profile = 'Analyst');

COMPANY    ENAME PROFILE
---------- ----- -------
SAP        Fred  Manager
SAP        John  Clerk

SQL>