How to Left Join from an un-nested table?

Question

I had written a code to create more records from 1 record based on some conditions. For example: If the user purchased a monthly package, the table will create records for each week until the Date Ended.

From this:

Order ID|Date Purchased| Date Ended| Package|
   001  | 2019-1-1     | 2019-2-1  | 1Month |

To this:

   Order ID|Date Purchased| Date Ended| Package| Date Weekly
      001  |2019-1-1      | 2019-2-1  | 1Month |  2019-1-1
      001  |2019-1-1      | 2019-2-1  | 1Month |  2019-1-8 
      001  |2019-1-1      | 2019-2-1  | 1Month |  2019-1-15
      001  |2019-1-1      | 2019-2-1  | 1Month |  2019-1-23

The question is how do I perform a Left Join of the 2nd table with the 1st table by Order ID? If there are additional records I wanted to join? For example, Order ID 002, 003 etc. Thanks in advance :)

Here's my code

SELECT
*
FROM 
(
SELECT
*,
DATE(PARSE_DATETIME('%Y-%m-%d %H:%M:%S', date_purchased)) AS date_purchased, --Transform datetime to time
DATE(PARSE_DATETIME('%Y-%m-%d %H:%M:%S', date_ended)) AS date_ended
FROM
`database_123`
),UNNEST(GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 WEEK)) AS date_weekly

score 0 · Answer 1 · answered Dec 12 '19 at 16:27

If I understand correctly your post

SELECT
*
FROM 
(
    SELECT
    *,
    DATE(PARSE_DATETIME('%Y-%m-%d %H:%M:%S', date_purchased)) AS date_purchased, --Transform datetime to time
    DATE(PARSE_DATETIME('%Y-%m-%d %H:%M:%S', date_ended)) AS date_ended
    FROM
    `database_123`
),UNNEST(GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 WEEK)) AS date_weekly 
LEFT JOIN your_first_table ft on date_weekly.orderId = ft.orederId

score 0 · Answer 2 · answered Dec 13 '19 at 14:43

You might consider to use CROSS JOIN function in order to combine records from two tables within joining each record from rows set across UNNEST() with the single row from the source table. Unlike the INNER JOIN or LEFT JOIN, CROSS JOIN doesn't establish a relationship between the joined tables, however it contains order_id column value for each generated by GENERATE_DATE_ARRAY record.

For example, as per given below test dataset:

WITH
  table_1 AS (
  SELECT
    "001" AS order_id,
    "2019-01-01 00:00:00" AS date_purch,
    "2019-02-01 00:00:00" AS date_end,
    "1Month" AS package
  UNION ALL
  SELECT
    "002" AS order_id,
    "2019-03-01 00:00:00" AS date_purch,
    "2019-05-01 00:00:00" AS date_end,
    "2Months" AS package)
SELECT
  *
FROM (
  SELECT
    order_id,
    DATE(PARSE_DATETIME('%Y-%m-%d %H:%M:%S',
        date_purch)) AS date_purchased,
    DATE(PARSE_DATETIME('%Y-%m-%d %H:%M:%S',
        date_end)) date_ended,
    package
  FROM
    table_1)
CROSS JOIN
  UNNEST(GENERATE_DATE_ARRAY(date_purchased, date_ended, INTERVAL 1 WEEK)) AS date_weekly
ORDER BY
  order_id

Find more examples here.

How to Left Join from an un-nested table?

2 Answers2