Filter table before applying the left join

Question

I have 2 tables, I want to filter 1 table before , the two 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 Left Join, so I get all the records from the Customer table, regardless of whether there are related records in the Entry table. However, I want to filter by category D in the input table before merging .

Desired Results:

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

If I run 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 will filter the last record.

So, I want all the rows from the left table to be attached to the records table, filtered by category D.

Thanks for any help in advance!

Answer 1

You need to move the WHERE filter to JOIN state:

 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

Answer 2

You can 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 feed here