应用左连接前过滤表 [英] Filter Table Before Applying Left Join
问题描述
我有 2 个表,我想在将 2 个表连接在一起之前过滤 1 个表.
I have 2 tables, I want to filter the 1 table before the 2 tables are joined together.
客户表:
╔══════════╦═══════╗
║ Customer ║ State ║
╠══════════╬═══════╣
║ A ║ S ║
║ B ║ V ║
║ C ║ L ║
╚══════════╩═══════╝
输入表:
╔══════════╦═══════╦══════════╗
║ Customer ║ Entry ║ Category ║
╠══════════╬═══════╬══════════╣
║ A ║ 5575 ║ D ║
║ A ║ 6532 ║ C ║
║ A ║ 3215 ║ D ║
║ A ║ 5645 ║ M ║
║ B ║ 3331 ║ A ║
║ B ║ 4445 ║ D ║
╚══════════╩═══════╩══════════╝
我想Left Join,所以我从Customer 表中获取所有记录,而不管Entry 表中是否有相关记录.但是我想在加入之前在条目表中过滤类别D.
I want to Left Join so I get all records from the Customer table regardless of whether there are related records in the Entry table. However I want to filter on category D in the entry table before the join.
预期结果:
╔══════════╦═══════╦═══════╗
║ Customer ║ State ║ Entry ║
╠══════════╬═══════╬═══════╣
║ A ║ S ║ 5575 ║
║ A ║ S ║ 3215 ║
║ B ║ V ║ 4445 ║
║ C ║ L ║ NULL ║
╚══════════╩═══════╩═══════╝
如果我要进行以下查询:
If I was to do the following query:
SELECT Customer.Customer, Customer.State, Entry.Entry
FROM Customer
LEFT JOIN Entry
ON Customer.Customer=Entry.Customer
WHERE Entry.Category='D'
这将过滤掉最后一条记录.
This would filter out the last record.
所以我想要左表中的所有行并将其连接到按类别 D 过滤的条目表中.
So I want all rows from the left table and join it to the entry table filtered on category D.
提前感谢您的帮助!!
推荐答案
您需要将 WHERE
过滤器移动到 JOIN
条件:
You need to move the WHERE
filter to the JOIN
condition:
SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Entry e
ON c.Customer=e.Customer
AND e.Category='D'
这篇关于应用左连接前过滤表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!