CODEWITHSUNDEEP
sqlarraysgoogle-bigqueryunnest
Matthew
Matthew

Reputation: 11

Copying down results from an ARRAY

I'm having issues with joining together separate tables what don't have anything in common. Essentially, I have a table with all possible customer IDs and a separate table for all possible dateweek ranges. I want to join them together in order to use at a later time to find date ranges for customer IDs where they are null in another dataset.

SELECT customer_id, ARRAY( SELECT DISTINCT CONCAT(year,week) AS yearweek FROM sales) FROM customers

While this works, I need to get the customer IDs to copy down in order to use it as a JOIN later on.

customer_id yearweek
4517507 202201
202202
202203
202204
202205
4517512 202201
202202
202203
202204
202205

In the end, I want it to look like this:

customer_id yearweek
4517507 202201
4517507 202202
4517507 202203
4517507 202204
4517507 202205
4517512 202201
4517512 202202
4517512 202203
4517512 202204
4517512 202205

Upvotes: 1

Views: 24

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

SELECT customer_id, yearweek 
FROM customers, (SELECT DISTINCT CONCAT(year,week) AS yearweek FROM sales)

Upvotes: 1

Related Questions