CODEWITHSUNDEEP
sqloracle-database
Kim Laxson
Kim Laxson

Reputation: 51

Sql query to find rows that only have one row (for exp license)

I have a query pulling data from several tables, including one called empl_access. I only need the active employees who only have a s_system_cd type of WEC.

There are two values for this field: WEC and WTC. Employees could have both, but I want employees who only have the one, WEC.

Here is my script so far. This pulls all WEC rows but some of them also have the WTC and I want them filtered out.

select DISTINCT a.empl_id, a.last_name, a.first_name, a.active_fl,    
   b.UDT09_ABBRV_ID, c.s_system_cd, count(a.empl_id) over ()
from empl a, V_TBE_MAX_EMPL_HISTORY b, empl_access c 
where a.empl_id=b.empl_id 
   and a.empl_id=c.empl_id
   and a.active_fl='Y'
   and c.s_system_cd like 'WEC%'
order by a.empl_id;

This is a sample of what is in the table:

empl_id    | last_name      | first_name     | udt09_abbr_id       | s_system_cd
-----------|----------------|----------------|---------------------|-----------------
000000     | Mouse          | Mickey         | 1111                | WEC
-----------|----------------|----------------|---------------------|-----------------
000000     | Mouse          | Mickey         | 1111                | WTC
-----------|----------------|----------------|---------------------|-----------------
000010     | Duck           | Donald         | 1111                | WEC
-----------|----------------|----------------|---------------------|-----------------
000010     | Duck           | Donald         | 1111                | WTC
-----------|----------------|----------------|---------------------|-----------------
000020     | Parker         | John           | 1150                | WEC
-----------|----------------|----------------|---------------------|-----------------
000030     | Smith          | Anne           | 1152                | WEC

This is what I want to pull

-----------|----------------|----------------|---------------------|-----------------
empl_id    | last_name      | first_name     | udt09_abbr_id       | s_system_cd
-----------|----------------|----------------|---------------------|-----------------
000020     | Parker         | John           | 1150                | WEC
-----------|----------------|----------------|---------------------|-----------------
000030     | Smith          | Anne           | 1152                | WEC
-----------|----------------|----------------|---------------------|-----------------

Upvotes: 4

Views: 190

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

SELECT DISTINCT a.empl_id, a.last_name, a.first_name, a.active_fl,    
   b.UDT09_ABBRV_ID, c.s_system_cd, count(a.empl_id) over ()
FROM empl a
INNER JOIN V_TBE_MAX_EMPL_HISTORY b ON a.empl_id=b.empl_id 
INNER JOIN empl_access c ON a.empl_id=c.empl_id AND c.s_system_cd like 'WEC%'
LEFT JOIN empl_access c2 ON a.empl_id=c2.empl_id AND c2.s_system_cd like 'WTC%'
WHERE a.active_fl='Y' AND c2.empl_id IS NULL
ORDER BY a.empl_id;

You could also do this with an EXISTS operation, or a GROUP BY and some fancy tricks with the aggregate functions (the min=max in the other answer works; so does checking the count = 1 and max = 'WEC').

The example in this answer is how you should always* write your joins.

The empl a, V_TBE_MAX_EMPL_HISTORY b, empl_access c stuff is for the birds. It gets harder to read and manage as you add more tables or conditions, there are certain things the old syntax just can't do, and the old syntax has been considered obsolete for roughly 20 years now.

* I don't use Oracle as often, but I understand there is a very rare situation that may require the older syntax. But outside of this (and you'll know if when you find it), stick to writing out INNER, LEFT, CROSS, FULL, LATERAL, etc.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142710

Would something like this help?

SQL> with test (empl_id, s_system_cd) as
  2    (select 1, 'WEC' from dual union all  -- EMPL_ID = 1 has both - we don't want it
  3     select 1, 'WTC' from dual union all
  4     select 2, 'WTC' from dual union all  -- EMPL_ID = 2 has only WTC - we don't want it
  5     select 3, 'WEC' from dual union all  -- EMPL_ID = 3 has only WEC - we do want it
  6     select 4, 'WEC' from dual union all  -- EMPL_ID = 4 has WEC, twice - we do want it
  7     select 4, 'WEC' from dual
  8    )
  9  select empl_id
 10  from test
 11  group by empl_id
 12  having min(s_system_cd) = max(s_system_cd)
 13     and min(s_system_cd) = 'WEC'
 14  order by empl_id;

   EMPL_ID
----------
         3
         4

SQL>

Upvotes: 2

Related Questions