How do I join 3 tables with 2 different ids (sql in snowflake)?-CodePudding

First, I need to unzip data from one table to transform into a new table. Secondly, I need to join another table to this new table based on customer id Z. Thirdly, I need to join yet another table to that second table using a different customer id, customer id Y.

Part 1: I have this data from Table A below.

NAME (Column 1)	VALUE (Column 2)	Customer_idZ (Coulmn 3)
account_status	ACTIVE	1234
card_template	Rewards	1234
customer_creation_date	1/8/2022	1234
enroll_store_code	pxweb	1234
enroll_store_name	Web Site	1234
push_opt_in	Yes	1234

I am transforming this data from Table A into a new table formatted as the below with this code

'''SELECT
Customer_idZ,
MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
 FROM Table A
GROUP BY Customer_idZ;

Customer_idZ	account_status	card_template	customer_creation_date	enroll_store_code	enroll_store_name	push_opt_in
1234	ACTIVE	PX Rewards	1/8/2022	pxweb	Web Site	Yes

Part 2: I want to then join another table (Table B) to this this newly formatted data based on the shared customer_idZ field. The data in Table B is below;

Customer_idZ	Status	Customer_idY
1234	ACTIVE	567890

Part 3: I need to join another table (Table C) to Table B based on the shared Customer_idY field. The data in Table C is below;

Customer_idY	Household_size	Children_present_in_household
567890	6	Yes

Final: I need the final output solely to be a list of customer_idY and customer_idZ.

I am not sure how to structure the multi-join needed between the three tables and need to pull ids for those profiles that have active status' in table a and have household sizes of 4 or more

CodePudding user response：

I just changed the column names in the query by @xQbert and hope it helps.

SELECT A.Customer_idZ
     , B.Customer_idY
     , account_status
     , card_template
     , customer_creation_date
     , enroll_store_code
     , enroll_store_name
     , push_opt_in
     , Household_size
     , Children_present_in_household
FROM (SELECT
      Customer_idZ,
      MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
      MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
      MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
      MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
      MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
      MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
      FROM  A
      GROUP BY Customer_idZ) A
INNER JOIN B
 on A.Customer_idZ = B.Customer_idZ
INNER JOIN C
 on B.Customer_idY = C.Customer_idY;

CodePudding user response：

I'm ussing a common table expression (CTE) "NEWA" to reformat your "A" table... we could make this an inline view but I find a CTE easier to read

We may need to change INNER JOIN to LEFT OUTER JOIN depending on how you want to handle when related records are not in associated tables...

With NewA As (SELECT
Customer_idZ,
MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
 FROM Table A
GROUP BY Customer_idZ)

SELECT Cusotmer_idZ
     , Customer_idY
     , account_status
     , card_template
     , customer_creation_date
     , enroll_store_code
     , enroll_store_name
     , push_opt_in
     , Household_size
     , Children_present_in_household
FROM NewA A
INNER JOIN B
 on A.Customer_idZ= B.Customer_idZ
INNER JOIN C
 on B.Customer_IDY = C.Customer_IDY
WHERE Household_size >= 4
  and account_status='Active'

We then use common join syntax to join on the relationships between the tables.

Now we could make these outer joins if you want all records from A and only those that match from B and all records from A combine b and those related records in C.

or we could use full outer joins to return all records from all tables. just depends on what you're after.

We could also use an inline view instead of a CTE:

SELECT A.Cusotmer_idZ
     , B.Customer_idY
     , account_status
     , card_template
     , customer_creation_date
     , enroll_store_code
     , enroll_store_name
     , push_opt_in
     , Household_size
     , Children_present_in_household
FROM (SELECT
      Customer_idZ,
      MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
      MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
      MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
      MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
      MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
      MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
      FROM Table A
      GROUP BY Customer_idZ) A
INNER JOIN B
 on A.Customer_idZ= B.Customer_idZ
INNER JOIN C
 on B.Customer_IDY = C.Customer_IDY
WHERE Household_size >= 4
  and account_status='Active'