Coder Perfect

Before using the left join, filter the table.

Category: sql

Tag: , , ,

Problem

I have two tables and want to filter one of them before joining the two tables 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        ║
   ╚══════════╩═══════╩══════════╝

I’d like to use a Left Join to get all of the records from the Customer table, regardless of whether or not there are any linked records in the Entry table. However, before the join, I’d like to filter on category D in the entry table.

Desired Results:

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

If I were to conduct the following search:

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

This would remove the most recent record.

So I want to link all of the data from the left table to the entry table, which is filtered on category D.

Thank you in advance for any assistance!!

Asked by Tom Jenkin

Solution #1

The WHERE filter must be moved 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

Answered by Taryn

Solution #2

Alternatively, you could:

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

Answered by Jeff Rosenberg

Solution #3

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'

Answered by c z

Post is based on https://stackoverflow.com/questions/15077053/filter-table-before-applying-left-join