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

Question

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?

Gordon Linoff · Accepted Answer · 2018-10-28 15:57:30Z

2

One method uses aggregation:

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

answered Oct 28, 2018 at 15:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2018-10-28 16:13:27Z

0

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

answered Oct 28, 2018 at 16:13

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

50.2k10 gold badges81 silver badges119 bronze badges

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2018-10-28 16:22:15Z

0

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'
    )

edited Oct 28, 2018 at 16:22

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

50.2k10 gold badges81 silver badges119 bronze badges

answered Oct 28, 2018 at 16:09

UjinT34

4,9971 gold badge14 silver badges28 bronze badges

1 Comment

Bob Jarvis - Слава Україні Over a year ago

I removed 'AS' before the table aliases, which is not allowed in Oracle. Only column aliases can be preceded by 'AS'.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related