SQL PIvot for a complex join

Question

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?

forpas forpas · Accepted Answer · 2021-02-01T20:24:50

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

SQL PIvot for a complex join

1 Answers