Menu
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!!
443
Normally, filtering is processed in the WHERE clause once the two tables have already been joined. It's possible, though that you might want to filter one or both of the tables before joining them. For example, you only want to create matches between the tables under certain circumstances.
When doing a left join in SQL any filtering of the table after the join will turn it into an inner join. However there are some easy ways to do the filtering first. Suppose you've got some tables related to a website. The pages table describes the different pages on the site.
In terms of readability though, especially in complex queries that have multiple joins, it is easier to spot join conditions when they are placed in the ON clause and filter conditions when they are placed in the WHERE clause.
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
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
138
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
38
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
