SQL - left join with restriction on right table?

[COAstroGeek]

Hi folks,
An SQL question.

Suppose I have the following tables:
mysql> SELECT * FROM customer;

+-------+----+
| name | id |
+-------+----+
| John | 1 |
| Jim | 2 |
| Jane | 3 |
| Jeff | 4 |
| Jill | 5 |
| Kelly | 6 |
+-------+----+

mysql> SELECT * FROM sale;
+------+---------------------+---------+
| item | epoch | cust_id |
+------+---------------------+---------+
| 1 | 2008-04-01 12:13:14 | 2 |
| 1 | 2008-05-01 12:13:14 | 1 |
| 4 | 2008-03-01 12:13:14 | 3 |
| 4 | 2008-03-17 12:13:14 | 5 |
| 4 | 2008-05-17 12:13:14 | 2 |
| 6 | 2008-04-17 12:13:14 | 1 |
| 6 | 2008-04-07 12:13:14 | 4 |
| 6 | 2008-03-09 12:13:14 | 4 |
| 8 | 2008-05-09 12:13:14 | 2 |
| 8 | 2008-05-19 12:13:14 | 1 |
| 9 | 2008-03-19 12:13:14 | 2 |
| 10 | 2008-04-18 12:13:14 | 3 |
+------+---------------------+---------+

Say I want a list of all the customers, and the items they've purchased - and I want the customer listed even if they haven't bought anything. Simple left join, right?

mysql> SELECT name, item FROM customer LEFT JOIN sale AN id=cust_id ORDER BY id, item;
+-------+------+
| name | item |
+-------+------+
| John | 1 |
| John | 6 |
| John | 8 |
| Jim | 1 |
| Jim | 4 |
| Jim | 8 |
| Jim | 9 |
| Jane | 4 |
| Jane | 10 |
| Jeff | 6 |
| Jeff | 6 |
| Jill | 4 |
| Kelly | NULL |
+-------+------+

Now, let's say I want the same query, but only list items bought before 2008-04-01. Again, list all the customers.

I thought I could do this - apply a constraint on the right table:
SELECT name, item FROM customer LEFT JOIN sale ON id=cust_id WHERE epoch < "2008-04-01 00:00:00" ORDER BY id, item;

But I get this result:
+------+------+
| name | item |
+------+------+
| Jim | 9 |
| Jane | 4 |
| Jeff | 6 |
| Jill | 4 |
+------+------+

It doesn't list all the customers?

Now, I can do a sub query on the right table to get the result I want:

mysql> SELECT name, item FROM customer LEFT JOIN (SELECT * FROM sale WHERE epoch < "2008-04-01 00:00:00") AS sale1 ON id=cust_id ORDER BY id, item;
+-------+------+
| name | item |
+-------+------+
| John | NULL |
| Jim | 9 |
| Jane | 4 |
| Jeff | 6 |
| Jill | 4 |
| Kelly | NULL |
+-------+------+

[chazzy]

you can also try this


SELECT name, item FROM customer
LEFT JOIN sale ON id=cust_id AND epoch < "2008-04-01 00:00:00"
ORDER BY id, item;

[COAstroGeek]

you can also try this


SELECT name, item FROM customer
LEFT JOIN sale ON id=cust_id AND epoch < "2008-04-01 00:00:00"
ORDER BY id, item;


Thanks - didn't know you could put the right table restriction in the ON clause.
Works perfectly!