sql - 如何在多个JOIN中获得联接列

Question

我正在尝试联接多个表(总共11个表)，并希望将Case_Id列组合为最终的联接结果。

帐户可以是一级保险，二级保险和/或两者。

当我运行以下查询时，我仅从JOIN“k”中获得Case_Id。如何查询以从JOIN“k”和“l”中获取Case_Id？我正在使用Claudera Hadoop。

例如，我有以下简化版本:

    SELECT DISTINCT a1.id AS Account_Id,
    k.Case_Id,
    k.Primary_Insurance_Payer_Name,
    l.Secondary_Insurance_Payer_Name
    FROM account a1 left outer JOIN  
    (
      SELECT k3.Name AS Primary_Insurance_Payer_Name,
      k3.Id AS Account_Id,
      k4.Id as Case_Id
      FROM patient k1
      JOIN patient_insurance k2
      ON k1.A360_Primary_Insurance__c=k2.Id
      JOIN account k3
      ON k2.A360_Payer__c=k3.Id
      left outer join pep_case k4
      on k4.enrolled_patient__c=k1.id     
    ) k ON a1.id=k.Account_Id left outer JOIN 
    (
      SELECT l3.Name AS Secondary_Insurance_Payer_Name,
      l3.Id AS Account_Id,
      l4.Id as Case_Id
      FROM patient l1
      JOIN patient_insurance l2
      ON l1.A360_Secondary_Insurance__c=l2.Id
      JOIN account l3
      ON l2.A360_Payer__c=l3.Id
      left outer join pep_case l4
      on l4.enrolled_patient__c=l1.id) l ON a1.id=l.Account_Id 

Answer 1

进行与处理保险付款人姓名类似的操作，在子查询中为其指定不同的别名，然后在最终的select子句中引用这些别名

SELECT DISTINCT      a1.id AS Account_Id    , k.k_Case_Id    , l.l_Case_Id    , k.Primary_Insurance_Payer_Name    , l.Secondary_Insurance_Payer_NameFROM account a1LEFT OUTER JOIN (      SELECT            k3.Name AS Primary_Insurance_Payer_Name          , k3.Id   AS Account_Id          , k4.Id   AS k_Case_Id      FROM patient k1      JOIN patient_insurance k2 ON k1.A360_Primary_Insurance__c = k2.Id      JOIN account k3 ON k2.A360_Payer__c = k3.Id      LEFT OUTER JOIN pep_case k4 ON k4.enrolled_patient__c = k1.id) k ON a1.id = k.Account_IdLEFT OUTER JOIN (      SELECT            l3.Name AS Secondary_Insurance_Payer_Name          , l3.Id   AS Account_Id          , l4.Id   AS l_Case_Id      FROM patient l1      JOIN patient_insurance l2 ON l1.A360_Secondary_Insurance__c = l2.Id      JOIN account l3 ON l2.A360_Payer__c = l3.Id      LEFT OUTER JOIN pep_case l4 ON l4.enrolled_patient__c = l1.id) l ON a1.id = l.Account_IdWHERE k_Case_Id  = l_Case_Id

---

EDIT

A single select query that joins the insurance information twice (see aliases ppi & spi) might work. nb: I also changed join from patient to pep_case into an inner join.

SELECT
      pac.Name AS Primary_Insurance_Payer_Name
    , sac.Name AS Secondary_Insurance_Payer_Name
    , pac.Id   AS Account_Id
    , pc.Id    AS Case_Id
FROM patient p
INNER JOIN pep_case pc           ON p.id = pc.enrolled_patient__c
INNER JOIN patient_insurance ppi ON p.A360_Primary_Insurance__c = ppi.Id
INNER JOIN account pac           ON ppi.A360_Payer__c = pac.Id
LEFT  JOIN patient_insurance spi ON p.A360_Secondary_Insurance__c = spi.Id
LEFT  JOIN account sac           ON spi.A360_Payer__c = sac.Id