CODEWITHSUNDEEP
google-bigquery
wizkids121
wizkids121

Reputation: 656

Four Table Join in BigQuery

Okay, so I'm trying to link together four different tables, and its getting very difficult. I provided snippets of each table in the hopes you all could help out

Table 1: data

+--------+--------+-----------+
| charge | amount |   date    |
+--------+--------+-----------+
|    123 |  10000 | 2/10/2016 |
|    456 |  10000 | 1/28/2016 |
|    789 |  10000 | 3/30/2016 |
+--------+--------+-----------+

Table 2: data_metadata

 +--------+------------+------------+
    | charge |    key     |   value    |
    +--------+------------+------------+
    |    123 | identifier | trrkfll212 |
    |    456 | code       | test       |
    |    789 | ID         | 123xyz     |
    +--------+------------+------------+

Table 3: buyer

  +-----+-----------+----------+----------+
| id  |   date    | discount |   plan   |
+-----+-----------+----------+----------+
| ABC | 2/13/2016 | yes      | option a |
| DEF | 2/1/2016  | yes      | option a |
| GHI | 1/22/2016 | no       | option a |
+-----+-----------+----------+----------+

Table 4: buyer_metadata

+--------------+-----------+--------+
| id |    |key|              | value  |
+--------------+-----------+--------+
| ABC          | migration | TRUE   |
| DEF          | emid      | foo    |
| GHI          | ID        | 123xyz |
+--------------+-----------+--------+

Okay, so the tables data and data_metadata are obviously connected by the charge column.

The tables buyer and buyer_metadata are connected by the id column.

But I want to link all of them together. I'm pretty sure the way to accomplish this is through linking the metadata tables together through the common field in the "value" column (in this example: 123xyz).

Could anyone help?

Upvotes: 0

Views: 97

Answers (2)

singhj
singhj

Reputation: 166

Let's take it in two steps, first create composite tables for data and buyer. Composite table for data:

SELECT data.charge, data.amount, data.date,
       data_metadata.key, data_metadata.value 
FROM [data] AS data  
JOIN (SELECT charge, key, value FROM [data_metadata]) AS data_metadata
ON data.charge = data_metadata.charge

And composite table for buyer:

SELECT buyer.id, buyer.date, buyer.discount, buyer.plan,
       buyer_metadata.key, buyer_metadata.value
FROM [buyer] AS buyer  
JOIN (SELECT key,  value FROM [buyer_metadata]) AS buyer_metadata
ON buyer.id = buyer_metadata.id

And then let's join the two composite tables

SELECT composite_data.*, composite_buyer.*
FROM (
    SELECT data.charge, data.amount, data.date,
           data_metadata.key, data_metadata.value 
    FROM [data] AS data  
    JOIN (SELECT charge, key, value FROM [data_metadata]) AS data_metadata
    ON data.charge = data_metadata.charge) AS composite_data
JOIN (
    SELECT buyer.id, buyer.date, buyer.discount, buyer.plan,
           buyer_metadata.key, buyer_metadata.value
    FROM [buyer] AS buyer  
    JOIN (SELECT key,  value FROM [buyer_metadata]) AS buyer_metadata
    ON buyer.id = buyer_metadata.id) AS composite_buyer

ON composite_data.value = composite_buyer.value

I haven't tested it but it's probably close.

For reference, here is the page on BigQuery JOINs. And have you seen this SO?

Upvotes: 1

Delphine
Delphine

Reputation: 888

This might look like something like that if all "link" columns are unique : 

SELECT * 
FROM data d
JOIN data_metadata dm ON d.charge = dm.charge
JOIN buyer_metada bm ON dm.value = bm.value
JOIN buyer b ON bm.id = b.id

If not, I think you'll have to use something like GROUP BY clause

Upvotes: 1

Related Questions