Joining two tables via a third mapping table in BigQuery / SQL-CodePudding

I was wondering if you could help me. I'd like to select a number of columns from table1 and merge them with some columns from table 2, using table 3 which maps the customer_id to cust_num.

Table 1

customer_id	account	balance	account_type
1	A	100	A
2	B	200	B
3	C	300	B

Table 2

cust_num	score1	score2	score3
1234	10	100	1000
2345	20	200	2000
3456	30	300	3000

Table 3

cust_id	ref
1234	1
2345	2
3456	3

In table 3 ref= table 1's customer_id and cust_id = table 2's "cust_num". I can't change any of the variable or column names.

Ideally I would like to select account and balance from table 1 and match them with score 1 and score 2 from table 2 to end up with

cust_num	account	balance	score1	score2
1234	A	100	10	100
2345	B	200	20	200
34567	C	300	30	300

Thanks in advance!

CodePudding user response：

Two inner joins should do the job:

SELECT cust_num,
   account,
   balance,
   score1,
   score2
FROM   ((table1
     INNER JOIN table3
             ON table3.ref = table1.customer_id)
    INNER JOIN table2
            ON table3.cust_id = table2.cust_num)

CodePudding user response：

Use join

SELECT t3.cust_id,t1.account,t1.balance,t3.score1,t3.score2
FROM table1 t1
JOIN table3 t2 ON t1.customer_id = t2.ref
JOIN table2 t3 ON t2.cust_id = t3.cust_num