连接Mssql中不相关的表

Question

我的任务是重新创建从oracle 11g数据库到MSSQL 2008数据库的视图。我被困在重新创建这个视图中:在甲骨文中，它是这样的：

CREATE OR REPLACE VIEW TRANSACTION_VIEW 
(FCT_TM_SOL_ID, FCT_TM_ACID, FCT_TM_RCRE_USER_ID, FCT_TM_TRAN_TYPE, 
FCT_TM_TRAN_SUB_TYPE,DA_SCHM_CODE,DC_GENDER,DC_CUST_TYPE_CODE,DA_ACCT_MGR_ID 
) 
AS 
SELECT  FCT_TM_SOL_ID,FCT_TM_ACID 
,FCT_TM_RCRE_USER_ID,FCT_TM_TRAN_TYPE,FCT_TM_TRAN_SUB_TYPE,dim_account.DA_SC 
HM_CODE, 
DC_GENDER,DC_CUST_TYPE_CODE,DA_ACCT_MGR_ID 
FROM FACT_TRANSACTION ,dim_account,dim_customer 
WHERE  FCT_TM_ACID = dim_account.DA_ACID(+) 
and dim_account.DA_CUST_ID =  dim_customer.DC_CUST_ID(+)
and dim_account.DA_ACCT_OWNERSHIP ='C';

FACT_TRANSACTION和dim_customer之间没有直接关系，所以我在MSSQL中重新创建视图时陷入了困境。到目前为止，我已经：

CREATE  VIEW TRANSACTION_VIEW 
(FCT_TM_SOL_ID, FCT_TM_ACID, FCT_TM_RCRE_USER_ID, FCT_TM_TRAN_TYPE, 
FCT_TM_TRAN_SUB_TYPE,DA_SCHM_CODE,DC_GENDER,DC_CUST_TYPE_CODE,DA_ACCT_MGR_ID 
) 
AS 
SELECT  t1.FCT_TM_SOL_ID,t1.FCT_TM_ACID 
,t1.FCT_TM_RCRE_USER_ID,t1.FCT_TM_TRAN_TYPE,t1.FCT_TM_TRAN_SUB_TYPE,t2.DA_SCHM_CODE, 
t3.DC_GENDER,t3.DC_CUST_TYPE_CODE,t3.DA_ACCT_MGR_ID 
FROM FACT_TRANSACTION t1 
left join dim_account t2 on t1.FCT_TM_ACID = t2.DA_ACID
left join ---STUCK HERE dim_customer t3
where t2.DA_ACCT_OWNERSHIP ='C';

请帮助如何加入与联合国有关的实体。

Answer 1

虽然FACT_TRANSACTION和dim_customer之间没有直接关系，但它们在dim_account中确实有共同点(尽管不同的字段)。因此，您可以在dim_account和dim_customer之间执行第二个左联接，如：

SELECT  t1.FCT_TM_SOL_ID,t1.FCT_TM_ACID, t1.FCT_TM_RCRE_USER_ID,t1.FCT_TM_TRAN_TYPE,t1.FCT_TM_TRAN_SUB_TYPE,t2.DA_SCHM_CODE, 
t3.DC_GENDER,t3.DC_CUST_TYPE_CODE,t3.DA_ACCT_MGR_ID 
FROM FACT_TRANSACTION t1 
left join dim_account t2 on t1.FCT_TM_ACID = t2.DA_ACID
left join dim_customer t3 on t2.DA_CUST_ID = t3.DC_CUST_ID
where t2.DA_ACCT_OWNERSHIP ='C';