# Before using the left join, filter the table.

## 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

## 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