Left join in google big query

Question

I have this error "ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table names.

This left join is used only for c.Network, for other cases it is not required. I understand the error but there is no common field between two tables (bigdata:RawDebug.T and bigdata:RawDebug.CarrierDetails). So i cannot reference two tables in "ON" statement.

Any help would be appreciated !!

 DEFINE QUERY Test2 
 SELECT 
 HardwareId, DebugReason, DebugData, 
 CASE 
  WHEN lower(DebugData) LIKE 'ver%' THEN 'Verizon'
  WHEN REGEXP_MATCH(DebugData,'\\d+') THEN c.Network
  ELSE REGEXP_REPLACE(DebugData,'\\?',' ')
 END
 as ActualDebugData 
 FROM (TABLE_DATE_RANGE([bigdata:RawDebug.T],TIMESTAMP ('2016-05-15'),TIMESTAMP('2016-05-15'))) as d
 LEFT JOIN [bigdata:RawDebug.CarrierDetails] as c ON c.Mcc = 289 AND c.Mnc = 88
  WHERE d.reason = 50013

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-05-16T19:44:39

SELECT 
  HardwareId, DebugReason, DebugData, 
  CASE 
    WHEN LOWER(DebugData) LIKE 'ver%' THEN 'Verizon'
    WHEN REGEXP_MATCH(DebugData,'\\d+') THEN c.Network
    ELSE REGEXP_REPLACE(DebugData,'\\?',' ')
  END AS ActualDebugData 
FROM (
  SELECT *
  FROM TABLE_DATE_RANGE([bigdata:RawDebug.T],TIMESTAMP ('2016-05-15'),TIMESTAMP('2016-05-15'))
) AS d
CROSS JOIN (
  SELECT Network 
  FROM [bigdata:RawDebug.CarrierDetails] 
  WHERE Mcc = 289 AND Mnc = 88 
  LIMIT 1 
) AS c 
WHERE d.reason = 50013

or to improve performance (per Felipe suggestion):

SELECT 
  HardwareId, DebugReason, DebugData, 
  CASE 
    WHEN LOWER(DebugData) LIKE 'ver%' THEN 'Verizon'
    WHEN REGEXP_MATCH(DebugData,'\\d+') THEN c.Network
    ELSE REGEXP_REPLACE(DebugData,'\\?',' ')
  END AS ActualDebugData 
FROM (
  SELECT 
    HardwareId, DebugReason, DebugData
  FROM TABLE_DATE_RANGE([bigdata:RawDebug.T],TIMESTAMP ('2016-05-15'),TIMESTAMP('2016-05-15'))
  WHERE reason = 50013      
) AS d
CROSS JOIN (
  SELECT Network 
  FROM [bigdata:RawDebug.CarrierDetails] 
  WHERE Mcc = 289 AND Mnc = 88 
  LIMIT 1 
) AS c

Left join in google big query

1 Answers