在应用左连接之前筛选表 [英] Filter Table Before Applying Left Join

问题描述

我有2个表格，我想在<2>表格连接在一起之前过滤1个表格。

客户表格： p>

 ╔════════════╗客户║州║
╠═════════
║A║S║
║B║V║
║C║ L║
╚════════
  

条目表：

 ╔═══════════════════ ═╦═════════
║客户║输入║类别║
╠══════════════════════════════ ══════════ $ b $ A║5575║D║$ b $b║A║6532║C║$ b $b║A║3215║D║$ b $b║A ║5645║M║
║B║3331║A║
║B║4445║D║
╚════════════════════ ╩════════
  

好的，我想离开加入，所以我得到无论Entry表中是否有相关记录，都可以从Customer表中记录所有记录。不过，我想在连接之前在条目表中对类别D进行过滤。

所需结果：

 ╔═════════════$ b║客户║ State║Entry║
╠══════════════$ b $ A║S║5575║
║A║S║3215║
║B║A║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'
  

<这将过滤掉最后一个r ecord。

所以我需要从左表中的所有行，并将它加入到D类过滤的入口表中。

感谢任何帮助提前!!

解决方案

您需要移动 WHERE 过滤到 JOIN 条件：

  SELECT c.Customer，c.State，e .Entry 
 FROM Customer c 
 LEFT JOIN条目e 
 ON c.Customer = e.Customer 
 AND e.Category ='D' 
  

请参阅带演示的SQL小提琴

I have 2 tables, I want to filter the 1 table before the 2 tables are joined together.

Customer Table:

    ╔══════════╦═══════╗
    ║ Customer ║ State ║
    ╠══════════╬═══════╣
    ║ A        ║ S     ║
    ║ B        ║ V     ║
    ║ C        ║ L     ║
    ╚══════════╩═══════╝

Entry Table:

   ╔══════════╦═══════╦══════════╗
   ║ Customer ║ Entry ║ Category ║
   ╠══════════╬═══════╬══════════╣
   ║ A        ║  5575 ║ D        ║
   ║ A        ║  6532 ║ C        ║
   ║ A        ║  3215 ║ D        ║
   ║ A        ║  5645 ║ M        ║
   ║ B        ║  3331 ║ A        ║
   ║ B        ║  4445 ║ D        ║
   ╚══════════╩═══════╩══════════╝

OK 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.

Desired Results:

   ╔══════════╦═══════╦═══════╗
   ║ Customer ║ State ║ Entry ║
   ╠══════════╬═══════╬═══════╣
   ║ A        ║ S     ║  5575 ║
   ║ A        ║ S     ║  3215 ║
   ║ B        ║ A     ║  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.

So I want all rows from the left table, and join it to the entry table filtered on category D.

Thanks to any help in advance!!

解决方案

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

这篇关于在应用左连接之前筛选表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！