如何将2个teradata查询合并为一个

Question

有人能帮我把这两个Teradata查询组合成一个查询吗？表cdb.dim_party_doc_id、cdb.dim_doc_issuer、mdb.fp_account_entity_map中没有customer_account_number，因此我无法在单个查询中直接连接所有这些表。非常感谢！！

SELECT
det.cust_id AS customer_account_number,
c.encrypt_val AS ssn_encrypted,
det.cust_first_name AS name_1,
bal.BALANCE_AMT AS principal 
FROM
cdb.DIM_CUSTOMER det 
INNER JOIN
cdb.fact_stored_val_acct_dly bal 
ON det.cust_id = bal.customer_id AND bal.curr_cd='USD' and bal.acct_type_code='SBA'
INNER JOIN
cdb.dim_party_acct_map b 
ON bal.customer_id = b.cust_id 
INNER JOIN
cdb.dim_party_doc_id c 
ON b.party_key = c.party_key 
AND c.status = 'A' 
INNER JOIN
cdb.dim_doc_issuer d 
ON c.doc_issuer_id = d.doc_issuer_id 
AND d.doc_type = 'TAX_ID' 
AND d.doc_subtype = 'SSN'         

和

SELECT 
own.owner_id AS customer_account_number,
entity.entity_id AS dd_number
FROM
mdb.fp_account_owner_map own
LEFT JOIN
mdb.fp_account_entity_map entity
ON own.fp_account_id = entity.fp_account_id
WHERE 
entity.entity_type in (12)
AND 
own.product_id in (5501)

Answer 1

下面的查询解决了我的问题

SELECT
det.cust_id AS customer_account_number,
temp.direct_deposit_account_number AS account_number,
c.encrypt_val AS ssn_encrypted,
det.cust_first_name AS name_1,
bal.BALANCE_AMT AS principal
FROM
cdb.DIM_CUSTOMER det 
LEFT JOIN
cdb.fact_stored_val_acct_dly bal 
ON det.cust_id = bal.customer_id AND bal.curr_cd='USD' and bal.acct_type_code='SBA'
INNER JOIN
cdb.dim_party_acct_map b 
ON bal.customer_id = b.cust_id 
INNER JOIN
cdb.dim_party_doc_id c 
ON b.party_key = c.party_key 
AND c.status = 'A' 
INNER JOIN
cdb.dim_doc_issuer d 
ON c.doc_issuer_id = d.doc_issuer_id 
AND d.doc_type = 'TAX_ID' 
AND d.doc_subtype = 'SSN'    
INNER JOIN 
(SELECT 
own.owner_id AS customer_id,
entity.entity_id AS direct_deposit_account_number
FROM mdb.fp_account_owner_map own 
LEFT JOIN
mdb.fp_account_entity_map entity
ON own.fp_account_id = entity.fp_account_id
WHERE entity.entity_type in (12)
AND own.product_id in (5501)) AS temp
ON customer_account_number=temp.customer_id