CODEWITHSUNDEEP
sqloracle-database
MDM1818
MDM1818

Reputation: 1

Oracle SQL - Return rows with a value based on multiple values in a second field

I need to return the rows that contain the employee names (in one field) who are only classified as Managers (not as Workers, or as Managers and Workers).

Managers and Workers values are in a second field.

So it might look like this:

+----------+------------+
| 'Miller' |  'Manager' |
| 'Jones'  |  'Manager' |
| 'Jones'  |  'Worker'  |
+----------+------------+

In this instance I just want it to return 'Miller'.

I can get one or both, but not the ones where an employee is only classified as a Manager.

Any thoughts?

Upvotes: 0

Views: 37

Answers (3)

UjinT34
UjinT34

Reputation: 4977

Method with a subquery which should work well when there are not only 'Managers' and 'Workers' in the table:

SELECT t1.name FROM t t1
WHERE
    t1.classification='Manager'
    AND NOT EXISTS (
        SELECT 1 FROM t t2 WHERE t1.name=t2.name AND t2.classification='Worker'
    )

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Count the number of titles. If they have a title of 'Manager' and there's only one title, select the individual:

SELECT *
  FROM PEOPLE p
  INNER JOIN (SELECT NAME, COUNT(TITLE) AS TITLE_COUNT
                FROM PEOPLE
                GROUP BY NAME) c
    ON c.NAME = p.NAME
  WHERE p.TITLE = 'Manager' AND
        c.TITLE_COUNT = 1;

dbfiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method uses aggregation:

select name
from t
group by name
having min(classification) = max(classification) and min(classification) = 'manager';

Upvotes: 2

Related Questions