加入拉出比预期多2行

Question

我有三张桌子--桌子tblFactorDefinition，tblFamily和tblConstituent。

• tblFactorDefinition在FieldName柱中含有腐蚀因子的FamilyID (即Factor1，Factor2，.....Factor9)。
• 表tblConstituent对家族中的每个成员都有关联的因子值( Factor1、Factor2、..Factor9 (如果存在的话)值)，并可以由FamilyID和tbLFacctorDefinition中的FamilyID连接。
• 表tblFamily包含家庭详细信息。(即FamilyTypeID=1是索引，FamilyTypeID=2是ETF)。

当试图在FamilyID中检索具有腐蚀因子值的因素时，我得到了行的2-3倍。例如，FamilyID =10216有27975个成分，但我的查询比55k+行多。我正努力找出加入的办法。

SELECT DISTINCT tc.FamilyID, 
                tfd.FieldName, 
                tc.Factor1, 
                tc.Factor2, 
                tc.Factor3, 
                tc.Factor4, 
                tc.Factor5, 
                tc.Factor6, 
                tc.Factor7, 
                tc.Factor8, 
                tc.Factor9, 
                tf.OpenDate 
FROM   soladbserver..tblFamily tf 
       JOIN soladbserver..tblFactorDefinition tfd 
         ON tfd.FamilyID = tf.FamilyID 
       JOIN soladbserver..tblConstituent tc 
         ON tc.FamilyID = tf.FamilyID 
            AND tc.StartDate <= Getdate() 
            AND tc.EndDate > Getdate() 
WHERE  tf.OpenDate = Cast(Getdate() AS DATE) 
       AND tf.FamilyTypeID = 1 
       AND tf.DataProviderID = 2 
       AND tf.FamilyID IN ( 10216 ) 

我期望有27975行的因子值可以腐蚀FieldName Factor1，Factor2，.，Factor9)，如果它们都有值的话。

​

​

​

​

​

​

屏幕截图1是tblConstituent表，Secreen快照2是tblFactorDefinition表，屏幕快照3,4,5是tblFamily表：

Answer 1

将联接更改为“左外接”，并使用sql子查询select语句提取字段名并查看您得到了什么。如果FamilyID在tc表中是主键，而在其他表中是外键，这将使您达到您想要的位置。

SELECT tf.FamilyID, 
            (Select top 1 isNull(tfd.FieldName,'') from soladbserver..tblFactorDefinition tfd 
     where tfd.FamilyID = tf.FamilyID ) as FieldName, -- this assumes each familyID only has one tfd.FieldName -- if not change both to left outer joins and leave the rest as is and run it
            tc.Factor1, 
            tc.Factor2, 
            tc.Factor3, 
            tc.Factor4, 
            tc.Factor5, 
            tc.Factor6, 
            tc.Factor7, 
            tc.Factor8, 
            tc.Factor9, 
            tf.OpenDate 
FROM   soladbserver..tblFamily tf 
  left outer JOIN soladbserver..tblConstituent tc 
     ON tc.FamilyID = tf.FamilyID 
        AND tc.StartDate <= Getdate() 
        AND tc.EndDate > Getdate() 
WHERE  tf.OpenDate = Cast(Getdate() AS DATE) 
   AND tf.FamilyTypeID = 1 
   AND tf.DataProviderID = 2 
   AND tf.FamilyID IN ( 10216 )