Filter Table Before Applying Left Join
SqlJoinFilterWhere ClauseSql Problem Overview
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 ║
╚══════════╩═══════╩══════════╝
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 ║ V ║ 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!!
Sql Solutions
Solution 1 - Sql
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'
Solution 2 - Sql
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
Solution 3 - Sql
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'