CODEWITHSUNDEEP
sqloracle-databasesqlitepivot-tabletranspose
More Than Five
More Than Five

Reputation: 10419

SQL PIvot for a complex join

I have one table which captures name value groups for a manager. So for example, manager with id 1, has two group value pairs: location=dublin and dept=sales.

GROUP VALUE MANAGERID
LOCATION DUBLIN 1
DEPT SALES 1
LOCATION OSLO 2
DEPT SALES 2
LOCATION LONDON 3
DEPT SALES 3

I have another table which has employee groupings...

GROUP VALUE EMPLOYEEID
LOCATION DUBLIN 101
DEPT SALES 101
LOCATION DUBLIN 102
DEPT SALES 102
LOCATION DUBLIN 103
DEPT SALES 103
SHOESIZE 15 103
LOCATION OSLO 104
DEPT SALES 104

I want to get a list of all employees that at a minimum have the same groupings as a specific manager. So for example, manager with id 1, is location=dublin and dept=sales and the employees that have these group values at a minimum are 101,102 and 103 but not 104.

I don't know the values for group column or the value column in advance. If I do a naieve inner join on the group and value columns and then a distinct, I get 101, 102 103 and 104

Any ideas?

Upvotes: 0

Views: 213

Answers (1)

forpas
forpas

Reputation: 164139

You can do it with a CROSS join of the tables and conditional aggregation:

SELECT e.EMPLOYEEID
FROM managers m CROSS JOIN employees e
WHERE m.MANAGERID = 1
GROUP BY e.EMPLOYEEID
HAVING COUNT(DISTINCT m."GROUP") = 
       COUNT(CASE WHEN e."GROUP" = m."GROUP" AND  e."VALUE" = m."VALUE" THEN 1 END)

See the demo.
Results:

EMPLOYEEID
101
102
103

Upvotes: 1

Related Questions