应用左连接之前的筛选器表

我有2个表，我想过滤1个表之前这2个表是连接在一起的。

顾客表:

   ╔══════════╦═══════╗
║ 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        ║
╚══════════╩═══════╩══════════╝

我想要左连接，这样我就可以从 Customer 表中获得所有记录，而不管 Entry 表中是否有相关记录。然而，我想在条目表 在加入之前中对类别 D 进行过滤。

预期效果:

   ╔══════════╦═══════╦═══════╗
║ Customer ║ State ║ Entry ║
╠══════════╬═══════╬═══════╣
║ A        ║ S     ║  5575 ║
║ A        ║ S     ║  3215 ║
║ B        ║ V     ║  4445 ║
║ C        ║ L     ║  NULL ║
╚══════════╩═══════╩═══════╝

如果我要做以下查询:

   SELECT Customer.Customer, Customer.State, Entry.Entry
FROM Customer
LEFT JOIN Entry
ON Customer.Customer=Entry.Customer
WHERE Entry.Category='D'

这会过滤掉最后一条记录。

因此，我希望从左边表格中的所有行都加入到 D 类别中过滤的条目表格中。

感谢任何提前帮助! ！

108818

小开

最佳答案

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'

See SQL Fiddle with Demo

小开

You could also do:

SELECT c.Customer, c.State, e.Entry
FROM Customer AS c
LEFT JOIN (SELECT * FROM Entry WHERE Category='D') AS e
ON c.Customer=e.Customer

SQL Fiddle here

小开

Or...

SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Entry e
ON c.Customer=e.Customer
WHERE e.Category IS NULL or e.Category='D'

小开

If you are using PostgreSQL i think that you can also use WITH clause to create Common Table Expression. This will especially be helpful if you are going to use this table in other Common Table Expressions in the same query.

Example:

 WITH
Filtered_Entries as (
SELECT Entry,Customer
FROM Entry_tbl
WHERE Entry = 'D'
)
SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Filtered_Entries e
ON c.Customer=e.Customer