Reputation: 13587
Here is what I have:
| customId | role | teamLeadIds |
| sl1 | salesRep | [tl1] |
| sl2 | salesRep | [tl1] |
| sl3 | salesRep | [tl2] |
| tl1 | teamLead | [tl3] |
| tl2 | teamLead | [tl3] |
| tl3 | teamLead | [] |
Here is what i need:
| customId | teamLeadIds |
| sl1 | [tl1, tl3] |
| sl2 | [tl1, tl3] |
| sl3 | [tl2, tl3] |
Here is what I do:
SELECT
users.customId,
ARRAY(SELECT DISTINCT teamLeadIds
FROM UNNEST(ARRAY_CONCAT(users.teamLeadIds, teamleads_1.teamLeadIds, teamleads_2.teamLeadIds)) teamLeadIds
ORDER BY teamLeadIds
) AS teamLeadIds
FROM ( SELECT * FROM users WHERE role = 'salesRep') users
JOIN ( SELECT * FROM users WHERE role = 'teamLead' ) teamleads_1 ON teamleads_1.customId IN UNNEST(users.teamLeadIds)
JOIN ( SELECT * FROM users WHERE role = 'teamLead' ) teamleads_2 ON teamleads_2.customId IN UNNEST(teamleads_1.teamLeadIds)
This works on this dataset:
WITH users AS (
SELECT 'sl1' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
SELECT 'sl2' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
SELECT 'sl3' AS customId, 'salesRep' AS role, ['tl2'] AS teamLeadIds UNION ALL
SELECT 'tl1' AS customId, 'teamLead' AS role, ['tl3'] AS teamLeadIds UNION ALL
SELECT 'tl2' AS customId, 'teamLead' AS role, ['tl3'] AS teamLeadIds UNION ALL
SELECT 'tl3' AS customId, 'teamLead' AS role, [] AS teamLeadIds
)
But doesn't work on this dataset (no 2nd level of nesting):
WITH users AS (
SELECT 'sl1' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
SELECT 'sl2' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
SELECT 'sl3' AS customId, 'salesRep' AS role, ['tl2'] AS teamLeadIds UNION ALL
SELECT 'tl1' AS customId, 'teamLead' AS role, [] AS teamLeadIds UNION ALL
SELECT 'tl2' AS customId, 'teamLead' AS role, [] AS teamLeadIds
)
Query returns no results in the latter case. Apparently, I need LEFT JOIN
, but it's not allowed:
LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.
Upvotes: 1
Views: 102
Reputation: 13587
Looks like CROSS JOIN
does the job:
WITH users AS (
SELECT 'sl1' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
SELECT 'sl2' AS customId, 'salesRep' AS role, ['tl1'] AS teamLeadIds UNION ALL
SELECT 'sl3' AS customId, 'salesRep' AS role, ['tl2'] AS teamLeadIds UNION ALL
SELECT 'sl4' AS customId, 'salesRep' AS role, ['tl4'] AS teamLeadIds UNION ALL
SELECT 'tl1' AS customId, 'teamLead' AS role, ['tl3'] AS teamLeadIds UNION ALL
SELECT 'tl2' AS customId, 'teamLead' AS role, ['tl3'] AS teamLeadIds UNION ALL
SELECT 'tl4' AS customId, 'teamLead' AS role, [] AS teamLeadIds UNION ALL
SELECT 'tl3' AS customId, 'teamLead' AS role, [] AS teamLeadIds
)
SELECT
users.customId,
ARRAY(SELECT DISTINCT teamLeadIds
FROM UNNEST(ARRAY_CONCAT(users.teamLeadIds, teamleads_1.teamLeadIds)) teamLeadIds
ORDER BY teamLeadIds
) AS teamLeadIds
FROM ( SELECT * FROM users WHERE role = 'salesRep') users
CROSS JOIN users teamleads_1 WHERE teamleads_1.role = 'teamLead' AND teamleads_1.customId IN UNNEST(users.teamLeadIds)
Upvotes: 1