如果满足条件，则联接表，否则不执行联接 [英] JOIN table if condition is satisfied, else perform no join

问题描述

我有一个表 transaction 和另一个表 transaction_item .

I have one table transaction and another table transaction_item.

一项交易有多个transaction_items.

One transaction has multiple transaction_items.

如果 transaction_item.amount> = 2 ，我想退出联接 transaction_item ，否则不执行联接.

I want to left join transaction_item if transaction_item.amount >= 2, else perform no join.

select ti.*
from transaction t
LEFT JOIN transaction_item ti on ti.unique_id = t.unique_id  
    AND ti.location_id =  t.location_id 
    AND ti.transaction_date = t.transaction_date
    AND ti.amount >= 2
where t.pos_transaction_id = 4220
and t.location_id = 1674
and t.transaction_date = '2020-05-08';

如果我这样做，结果是 15 行而不是总共 20 行，因为 20 行在 transaction_item 表中对应于 transaction_id 4220.但是我在这种情况下不希望加入，因为 transaction_item 中的5行的 amount<2 .

If I do it this way it is results in 15 rows in place of total 20 rows, because 20 rows are in transaction_item table corresponding to transaction_id 4220. But I want no join in this case because 5 rows in transaction_item have amount < 2.

推荐答案

您的描述有些混乱，但是标题说:
否则不执行加入"

Your description a bit confusing, but the title says:
"else perform no join"

所以我想你想要这样的东西:

So I think you want something this:

SELECT ti.*
FROM   transaction t
LEFT   JOIN transaction_item ti ON ti.unique_id        = t.unique_id  
                               AND ti.location_id      = t.location_id 
                               AND ti.transaction_date = t.transaction_date
                               AND NOT EXISTS (
                                    SELECT FROM transaction_item tx
                                    WHERE  tx.unique_id        = t.unique_id  
                                    AND    tx.location_id      = t.location_id 
                                    AND    tx.transaction_date = t.transaction_date
                                    AND    tx.amount < 2)  -- !
WHERE  t.pos_transaction_id = 4220
AND    t.location_id = 1674
AND    t.transaction_date = '2020-05-08';

但是， LEFT JOIN 与仅在 SELECT 列表中的 right 表中的列结合在一起，最多是可疑的.这样，您可以获得一行，且 transaction 中的每一行都具有所有NULL值，并且在 transaction_item 或中没有匹配行，或者一个或多个匹配项具有 amount<的行2 .我怀疑您想要那个.

However, the LEFT JOIN in combination with only columns from the right table in the SELECT list is dubious at best. This way you get one row with all NULL values for every row in transaction that qualifies and has no matching rows in transaction_item or one or more matching rows with amount < 2. I doubt you want that.

通常，您需要将 transaction 中的列包括在 SELECT 列表中，或者改用 [INNER] JOIN .

Typically, you'll want to include columns from transaction in the SELECT list, or use an [INNER] JOIN instead.

所以可能是:

SELECT ti.*
FROM   transaction t
JOIN   transaction_item ti USING (unique_id, location_id, transaction_date)
WHERE  t.pos_transaction_id = 4220
AND    t.location_id = 1674
AND    t.transaction_date = '2020-05-08';
AND    NOT EXISTS (
   SELECT FROM transaction_item tx
   WHERE  tx.unique_id        = t.unique_id  
   AND    tx.location_id      = t.location_id 
   AND    tx.transaction_date = t.transaction_date
   AND    tx.amount < 2);

然后再次，表 transaction_item 通常会具有引用 transaction 的FK列 transaction_id -在这种情况下，我们可以简化

Then again, a table transaction_item would typically have a FK column transaction_id referencing transaction - in which case we can simplify

SELECT ti.*
FROM   transaction t
JOIN   transaction_item ti ON ti.transaction_id = t.transaction_id -- ?
WHERE  t.pos_transaction_id = 4220
AND    t.location_id = 1674
AND    t.transaction_date = '2020-05-08';
AND    NOT EXISTS (
   SELECT FROM transaction_item tx
   WHERE  tx.transaction_id = t.transaction_id
   AND    tx.amount < 2);

您提到了 pos_transaction_id 和 transaction_id ，所以我想在这里...

You mentioned both pos_transaction_id and transaction_id, so I am guessing here ...

要摆脱的主要见解: 总是 显示带有任何此类问题的确切表定义.

The major insight to take away from this: always show exact table definitions with any such question.

这篇关于如果满足条件，则联接表，否则不执行联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！