CODEWITHSUNDEEP
google-bigquery
Selch
Selch

Reputation: 131

Inner join to another another table in my complex query

I've created a static table that I want join to this query to add to my results. The table is named 'tablets' and contains fields: branch, tablet_id, imei and phone.

I want to match on IMEI and have branch, tablet_id and phone added as columns to the result. However, I don't understand how to do so due to the complexity of this query.

SELECT
  event_dim.date AS Date,
  (
  SELECT
    value.value.string_value
  FROM
    UNNEST(user_dim.user_properties)
  WHERE
    key = 'imei_id') AS IMEI,
  (
  SELECT
    value.value.string_value
  FROM
    UNNEST(user_dim.user_properties)
  WHERE
    key = 'division_id') AS Branch,
  (
  SELECT
    value.value.string_value
  FROM
    UNNEST(user_dim.user_properties)
  WHERE
    key = 'truck_id') AS Truck,
  (
  SELECT
    value.value.string_value
  FROM
    UNNEST(user_dim.user_properties)
  WHERE
    key = 'installer_id') AS Installer,
  COUNT(*) AS Count
FROM
  `smarttruck-6d137.com_usiinc_android_ANDROID.app_events_*`
CROSS JOIN
  UNNEST(event_dim) AS event_dim
WHERE
  event_dim.name = 'app_exception'
  AND EXISTS (
  SELECT
    1
  FROM
    UNNEST(event_dim.params)
  WHERE
    key = 'fatal'
    AND value.int_value = 1 )
  AND REGEXP_EXTRACT(_TABLE_SUFFIX, r'(\d+)') BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY
  Date,
  IMEI,
  Branch,
  Truck,
  Installer
ORDER BY
  Date DESC,
  Count DESC;

Upvotes: 0

Views: 72

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Assuming main query in you question is correct - try simple LEFT JOIN on what you already have 

#standardSQL
SELECT 
  date, main.IMEI, main.Branch, Truck, Installer, count,
  tablets.branch, tablet_id, phone
FROM (
  SELECT
    event_dim.date AS DATE,
    (SELECT value.value.string_value FROM UNNEST(user_dim.user_properties) WHERE key = 'imei_id') AS IMEI,
    (SELECT value.value.string_value FROM UNNEST(user_dim.user_properties) WHERE key = 'division_id') AS Branch,
    (SELECT value.value.string_value FROM UNNEST(user_dim.user_properties) WHERE key = 'truck_id') AS Truck,
    (SELECT value.value.string_value FROM UNNEST(user_dim.user_properties) WHERE key = 'installer_id') AS Installer,
    COUNT(*) AS COUNT
  FROM `smarttruck-6d137.com_usiinc_android_ANDROID.app_events_*`
  CROSS JOIN UNNEST(event_dim) AS event_dim
  WHERE event_dim.name = 'app_exception'
    AND EXISTS (SELECT 1 FROM UNNEST(event_dim.params) WHERE key = 'fatal' AND value.int_value = 1 )
    AND REGEXP_EXTRACT(_TABLE_SUFFIX, r'(\d+)') BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
  GROUP BY DATE, IMEI, Branch, Truck, Installer
) main
LEFT JOIN `yourproject.yourdataset.tablets` AS tablets
ON tablets.imei = main.IMEI
ORDER BY date DESC, count DESC

Upvotes: 2

Related Questions