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

具有多个连接的意外查询结果-mysql

join mysql sql

2

EmmyS · 技术社区 · 14 年前

为了澄清这一点,下面是这些桌子的实际外观。

ID   |   PurchAmt   |   AcctID    | ShipAddrID
================================================
1    |   30.99      |   25        |  420
2    |   45.22      |   31        |  209

用户表

ID   |   Name       
=================================
25   |   Anastasia Beaverhausen   
31   |   Charles Beaverhausen
45   |   Bennie Beaverhausen

地址表

ID   |   customer_id   | name                     |  address          
==========================================================================
300  |   25            | Anastasia Beaverhausen   | 123 Park Avenue  
209  |   31            | Charles Beaverhausen     | 500 5th Avenue   
420  |   45            | Bennie Beaverhausen      | 500 North Michigan Avenue

我需要做的是返回如下内容:

PurchaseID   |  PurchAmt  | billname                   |billAddress        |shipName           | shipaddress
====================================================================================================================================
1            |  30.99     | Anastasia Beaverhausen     |123 Park Avenue    |Bennie Beaverhausen  |500 North Michigan Avenue

因此,我需要通过purchases.AcctID=addresses.customer_id关系将购买连接到地址来获取billaddress;然后通过purchases.ShipAddrID=addresses.id关系将购买直接连接到地址来获取shipaddress。不管怎么说,这在我脑子里是有道理的。但是当我运行查询时,每个购买ID会得到多行,如下所示:

PurchActvtyID | billName           | billAddress1             |shipName         |   shipAddress1
==================================================================================================================================
1535    |       Anastasia Beaverhausen | 123 Park Avenue          |Bennie Beaverhausen  | 500 North Michigan Avenue
1535    |       Bennie Beaverhausen    | 500 North Michigan Avenue  | Bennie Beaverhausen   | 500 North Michigan Avenue

有人能解释为什么会这样吗?我确信这可能与使用哪种连接有关,但无论我尝试哪种连接,似乎都无法获得正确的结果。我的问题是:

SELECT p.PurchActvtyID, a1.name AS billName, a1.address1 AS billAddress1, a2.name AS shipName, a2.address1 AS shipAddress1
FROM arrc_PurchaseActivity p
LEFT OUTER JOIN jos_customers_addresses a1 ON p.AcctID = a1.customer_id
LEFT OUTER JOIN jos_customers_addresses a2 ON p.ShipAddrID = a2.id

ORDER BY p.PurchActvtyID ASC

编辑

Stephen的查询结果:

PurchActvtyID  |    billName   |    shipName   |    billAddress  |  shipAddress
========================================================================================

1535  | Esther Strom | Esther Strom |123 Park Avenue | 500 North Michigan Avenue

1535  | Esther Strom | Esther Strom |500 North Michigan Avenue |500 North Michigan Avenue

该名称与我在所需结果示例中显示的名称不同的原因是,您的查询正在从users表中提取名称,这是不准确的-用户名不一定与与与给定用户关联的帐单或发货名称相同。这就是为什么我需要从addresses表而不是users表中提取这些值。

4 回复 | 直到 14 年前

1

user359040 user359040 14 年前

尽管没有这样的重复,但是对于一个客户ID,您可以有多个地址-这就是示例中出现的情况,因为一次购买(1535)返回多个帐单地址(公园大道123号和北密歇根大道500号)。

能够有许多帐单地址(随着时间的推移),尽管通常每个事务只有一个帐单地址。因此,我建议在arrc_PurchaseActivity中添加一个BillAddrID字段(如果它还没有),并将到jos_customers_addresses别名a1的链接更改为 p.BillAddrID = a2.id .

编辑,以下评论:

以下查询应解决在帐单别名上返回多个地址的问题:

SELECT p.PurchActvtyID, a1.name AS billName, a1.address1 AS billAddress1, a2.name AS shipName, a2.address1 AS shipAddress1
FROM arrc_PurchaseActivity p
LEFT OUTER JOIN jos_customers_addresses a1 ON p.AcctID = a1.customer_id and a1.billing = 1
LEFT OUTER JOIN jos_customers_addresses a2 ON p.ShipAddrID = a2.id
ORDER BY p.PurchActvtyID ASC

2

1

Michael Riley - AKA Gunny 14 年前

要创建所需的输出,只需要两个表:
桌子
地址桌子

地址桌子两次:
帐单地址
一次为 送货地址

SELECT
t1.ID       as PurchaseID,
t1.PurchAmt as PurchAmt,
t2.name     as billname,
t2.address  as billaddress,
t3.name     as shipname,
t3.address  as shipaddress
FROM Purchases t1
INNER JOIN Addresses t2
        ON t1.AcctID
         = t2.customer_id
INNER JOIN Addresses t3
        ON t1.ShipAddrID
         = t3.ID

这个链接到 演员表 信息
这个 第二个内部连接 航运信息

3

0

EmmyS 14 年前

我确实在专家交流会上得到了答案。我只需要用一个GROUPBY子句。所以我的问题是这样的:

SELECT p.PurchActvtyID, a1.name AS billName, a1.address1 AS billAddress1, a2.name AS shipName, a2.address1 AS shipAddress1
FROM arrc_PurchaseActivity p
LEFT OUTER JOIN jos_customers_addresses a1 ON p.AcctID = a1.customer_id
LEFT OUTER JOIN jos_customers_addresses a2 ON p.ShipAddrID = a2.id
GROUP BY p.PurchActvtyID
ORDER BY p.PurchActvtyID ASC

4

-2

JNK 14 年前

我认为没有理由根据你的标准加入同一个表两次。。。

尝试:

SELECT p.PurchActvtyID, a1.name AS billName, a1.address1 AS billAddress1, a1.name AS shipName, a1.address1 AS shipAddress1
FROM arrc_PurchaseActivity p
LEFT OUTER JOIN jos_customers_addresses a1 ON p.AcctID = a1.customer_id AND p.ShipAddrID = a1.id

ORDER BY p.PurchActvtyID ASC

编辑:

我怀疑这是因为您正在对同一个表执行两个左外部连接,使用不同的条件。