代码之家 › 专栏 › 技术社区 › TomH

关联表上的左联接

outer-join mysql sql

TomH · 技术社区 · 14 年前

我有三张桌子

prospect——保存prospect信息

id
name
projectID

潜在客户示例数据

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

联合——保存联合信息

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语句,它将提取给定项目的每个潜在客户的所有联合值,如果给定联合和潜在客户的联合prospect表中没有值,则显示空值。

在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

我试过在prospect和conjunit表上使用一个内部联接,然后在conjunitprospect上使用一个左联接,但是在某个地方,我得到了prospect/conjunit对的笛卡尔积,这对我来说毫无意义。

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

非常感谢您的指导!!

2 回复 | 直到 14 年前

DRapp 14 年前

那这对你有用…通过一个select作为您的第一个from table,对该项目中的所有潜在客户和元素预加入笛卡尔。然后,左键加入联合展望。显然,您可以从结果中更改/删除某些列,但至少所有列都存在,在您希望得到的连接中有您期望的确切结果…

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

Ryan Elkins 14 年前

您的笛卡尔产品是通过项目ID连接的结果-在您的示例数据中,有3个项目ID为1的潜在客户和3个项目ID为1的潜在客户。然后,基于项目ID的联接将产生9行数据,这就是您要得到的。看起来您真的需要通过conjuntprospects表进行连接,因为它支持潜在客户和联合客户之间的映射。

如果你尝试以下方法会怎么样:

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

不确定这是否可行,但看起来联合预测需要位于连接的中心,以便正确地将潜在客户映射到联合。