关联表上的左连接

Question

我有三个表

Prospect — 保存潜在客户信息

id
name
projectID

Prospect 的示例数据

id | name | projectID
1  |  p1  |  1
2  |  p2  |  1
3  |  p3  |  1
4  |  p4  |  2
5  |  p5  |  2
6  |  p6  |  2

Conjoint — 保存联合信息

id
title
projectID

示例数据

id | title  | projectID
1  | color  |  1
2  | size   |  1
3  | qual   |  1
4  | color  |  2
5  | price  |  2
6  | weight |  2

有一个关联表，用于保存潜在客户的联合值：

ConjointProspect
id
prospectID
conjointID
value

示例数据

id | prospectID | conjointID | value
1  |      1     |      1     |   20
2  |      1     |      2     |   30
3  |      1     |      3     |   50
4  |      2     |      1     |   10
5  |      2     |      3     |   40

中有一个或多个潜在客户以及一个或多个联合数据他们各自的表。潜在客户对于每个联合词可能有也可能没有值。

我想要一个 SQL 语句，该语句将提取给定项目的每个潜在客户的所有联合值，如果给定联合和潜在客户的 ConjointProspect 表中没有不存在的值，则显示 NULL。

对于projectID = 1，

prospectID  | conjoint ID  | value
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    2       |      1       |   10
    2       |      2       |   NULL
    2       |      3       |   40
    3       |      1       |   NULL
    3       |      2       |   NULL
    3       |      3       |   NULL

我尝试在前景和联合表上使用内部联接，然后在ConjointProspect上使用左联接，但在某个地方我得到了前景/联合对的笛卡尔积，但不' （对我来说）没有任何意义

SELECT p.id, p.name, c.id, c.title, cp.value
FROM prospect p
INNER JOIN  conjoint c ON p.projectID = c.projectid
LEFT JOIN conjointProspect cp ON cp.prospectID = p.id
WHERE  p.projectID = 2
ORDER BY p.id, c.id

prospectID  | conjoint ID  | value
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    2       |      1       |   10
    2       |      2       |   40
    2       |      1       |   10
    2       |      2       |   40
    2       |      1       |   10
    2       |      2       |   40
    3       |      1       |   NULL
    3       |      2       |   NULL
    3       |      3       |   NULL

非常感谢指导！！

Answer 1

那么这将对您有用...通过选择作为您的第一个 FROM 表，针对该项目中的所有前景和元素预先加入笛卡尔。然后，左连接到 conjoinprospect。显然，您可以更改/删除结果中的某些列，但至少所有列都在那里，在您想要的连接中，并具有您期望的确切结果......

SELECT 
       PJ.*,
       CJP.Value
    FROM 
       (   SELECT 
                   P.ID ProspectID,
                   P.Name,
                   P.ProjectID,
                   CJ.Title,
                   CJ.ID ConJointID
               FROM 
                   Prospect P,
                   ConJoint CJ
               where 
                       P.ProjectID = 1
                   AND P.ProjectID = CJ.ProjectID
               ORDER BY 
                   1, 4
         ) PJ
         LEFT JOIN conjointProspect cjp 
             ON PJ.ProspectID = cjp.prospectID 
            AND PJ.ConjointID = cjp.conjointid
     ORDER BY 
        PJ.ProspectID,
        PJ.ConJointID

Answer 2

您的笛卡尔积是按项目 ID 加入的结果 - 在您的示例数据中，有 3 个项目 id 为 1 的潜在客户和 3 个项目 id 为 1 的联合体。基于项目 id 的加入应该会产生 9 行数据，这就是你得到的。看起来您确实需要通过 conjointprospects 表加入，因为它保存了前景和联合之间的映射。

如果您尝试以下操作该怎么办：

SELECT p.id, p.name, c.id, c.title, cp.value
FROM prospect p
LEFT JOIN conjointProspect cp ON cp.prospectID = p.id
RIGHT JOIN conjoint c ON cp.conjointID = c.id
WHERE  p.projectID = 2
ORDER BY p.id, c.id

不确定这是否有效，但似乎联合前景需要位于连接的中心，以便正确地将前景映射到联合。