ON子句中存在条件的意外LEFT JOIN行为

Question

我有以下三个表：

• fees-包含账单上的费用信息
• payments-具有账单付款信息(包括费用付款(
• details-有关于付款的详细信息

我很难理解为什么我的查询没有返回我期望的结果。

SQL Fiddle:sqlfiddle.com/#！942636/3

CREATE TABLE fees (
receipt_number int(11) NOT NULL,
bill_number    int(11) NOT NULL,
version        int(11) NOT NULL,
fee_id         int(11) NOT NULL,
fee_type       varchar(30) NOT NULL
);
CREATE TABLE payments (
receipt_number int(11) NOT NULL,
bill_number    int(11) NOT NULL,
version        int(11) NOT NULL,
payment_id     int(11) NOT NULL,
amount         decimal(13,2) NOT NULL DEFAULT '0.00'
);
CREATE TABLE details (
receipt_number int(11) NOT NULL,
payment_id     int(11) NOT NULL,
fee_type       varchar(30) DEFAULT NULL
amount         decimal(13,2) NOT NULL DEFAULT '0.00'
);
INSERT INTO fees (receipt_number, bill_number, version, fee_id, fee_type)
VALUES (111, 100, 1, 1, 'a'),
(111, 100, 1, 1, 'b'),
(111, 100, 1, 2, 'c'),
(111, 100, 1, 2, 'd');
INSERT INTO payments (receipt_number, bill_number, version, payment_id, amount)
VALUES (111, 100, 1, 98, 30.00),
(111, 100, 1, 99, 60.00);
INSERT INTO details (receipt_number, payment_id, fee_type, amount)
VALUES (111, 98, 'a', 10.00),
(111, 98, 'b', 10.00),
(111, 98, 'd', 10.00),
(111, 99, 'a', 20.00),
(111, 99, 'b', 20.00),
(111, 99, 'c', 20.00);

我正试图找出是否：

• 账单的费用类型为"c">
• 已支付该费用(由fee_type决定(

我的查询：

SELECT    fees.bill_number, details.receipt_number AS has_payment_type_c
FROM      fees
LEFT JOIN payments
USING (bill_number, version)
LEFT JOIN details
ON  details.receipt_number = payments.receipt_number
AND details.payment_id     = payments.payment_id
AND details.fee_type = 'c'
WHERE     fees.fee_type = 'c'
AND       details.receipt_number IS NULL;

我得到的结果：

bill_number  has_payment_type_c
100          (null)

我不应该看到此列表中的bill_number，因为账单有费用和该费用的付款。

我正在筛选ON子句中的details.fee_type，以减少在该表中查找的记录数，并仅加入该特定fee_type的表。该查询似乎"；工作；(0个结果(，将fee_type联接条件移动到WHERE子句，但我认为这是不对的。

我的问题：

1. 我在查询中做错了什么？我如何修复它以产生我想要的结果，这种修复是如何工作的
2. 假设我得到了一个有效的查询，我是否可以简单地反转表格顺序和SELECT来找到相反的信息——某个fee_type的付款，而没有相同类型的相关费用记录

Answer 1

答案是使用一个内部联接payments和details的派生表，然后将派生表留给费用联接：

SELECT    DISTINCT fees.bill_number, fees.version, x.bill_number AS has_payment_type_c
FROM      fees
LEFT JOIN (
SELECT payments.bill_number, payments.version, details.fee_type
FROM   payments
JOIN   details
USING(receipt_number, payment_id)
WHERE  details.fee_type = 'c'
) x
ON  fees.bill_number = x.bill_number
AND fees.version     = x.version
WHERE    fees.fee_type = 'c'
AND      x.bill_number IS NULL;

这将返回预期的0结果，并且可以通过删除检查空x.bill_number:的where条件进行验证

bill_number  version  has_payment_type_c
100          1        100

同样的方法也适用于寻找免费付款。使用内部联接付款和详细信息的派生表，然后将联接留给费用。选择fee.bill_number AS has_payment_type_c并将WHERE fee.bill_number IS NULL添加到WHERE子句中。