CODEWITHSUNDEEP
sqljoinfilterwhere-clause
Tom Jenkin
Tom Jenkin

Reputation: 2065

Filter Table Before Applying Left Join

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

Upvotes: 122

Views: 152021

Answers (4)

TLamp
TLamp

Reputation: 121

If you are using PostgreSQL i think that you can also use WITH clause to create Common Table Expression. This will especially be helpful if you are going to use this table in other Common Table Expressions in the same query.

Example:

 WITH 
      Filtered_Entries as (
     SELECT Entry,Customer
       FROM Entry_tbl
       WHERE Entry = 'D'
    )
SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Filtered_Entries e
   ON c.Customer=e.Customer

Upvotes: 1

c z
c z

Reputation: 8967

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'

Upvotes: 2

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

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

Upvotes: 40

Taryn
Taryn

Reputation: 247680

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

Upvotes: 168

Related Questions