CODEWITHSUNDEEP
joinleft-joingoogle-bigquery
user3447653
user3447653

Reputation: 4148

Left join in google big query

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

Upvotes: 1

Views: 1591

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

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

Upvotes: 1

Related Questions