CODEWITHSUNDEEP
sqlgoogle-bigquery
stkvtflw
stkvtflw

Reputation: 13587

BigQuery: LEFT JOIN without field equality

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

Answers (1)

stkvtflw
stkvtflw

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

Related Questions