Mixed Inner and Left Join SQL Statement

[Chris Fowler]

I am needing help with a mixed left and inner join SQL statement in 
MySQL 4.0 (ie, can't use subselects yet like 4.1).

I guess my point of confusion is that if I do a join like table_1 left 
join table_2 inner join table_3 (assuming the clauses only reference 
each of the 2 tables in the order written), i expect to get all of the 
results from table_1 - where am I thinking incorrectly and how do I 
accomplish the following?

Here are the specifics of the situation:
3 Tables (2 main and 1 associative)
*event table*
id
name
datetime_start
datetime_end
*resource table*
id
name
*event_resources table*
event_id
resource_id
I need to list ALL of the resources from the resource table and show if 
there is a conflict with any existing events by showing the name of the 
event, otherwise, the name of the event should be NULL. There will be a 
given datetime_start and a datetime_end to show conflicts against.

The result set I need would look like this:
+--------+--------------------+----------------+
| res_id | res_name           | event_name     |
+--------+--------------------+----------------+
|      1 | TV/VCR             | Event One      |
|      2 | Overhead Projector | Event Two      |
|      3 | Table and Chairs   | NULL           |
|      4 | Artboard           | NULL           |
+--------+--------------------+----------------+
event table data
+-----------------------+---------------------+---------------------+
| name                  | datetime_start      | datetime_end        |
+-----------------------+---------------------+---------------------+
| Event One             | 2003-09-30 19:00:00 | 2003-09-30 21:00:00 |
| Event Two             | 2003-09-30 19:30:00 | 2003-09-30 21:30:00 |
+-----------------------+---------------------+---------------------+
event_resources table data
+----------+-------------+
| event_id | resource_id |
+----------+-------------+
|      248 |           1 |
|      250 |           2 |
+----------+-------------+
Given start time of '2003-09-30 20:00:00' and end time of '2003-09-30 
22:00:00'.

Here is the (incorrect) SQL statement as I am trying it right now:
select resource.name as res_name, event.name as event_name from 
resource left join event_resources on resource.id = 
event_resources.resource_id inner join event on 
event_resources.event_id = event.id and ((event.datetime_start > 
'2003-09-30 20:00:00' and event.datetime_start < '2003-09-30 22:00:00') 
OR (event.datetime_end > '2003-09-30 20:00:00' and event.datetime_end < 
'2003-09-30 22:00:00'));

I get the following result:
+--------+--------------------+------------+
| res_id | res_name           | event_name |
+--------+--------------------+------------+
|      1 | TV/VCR             | Event One  |
|      2 | Overhead Projector | Event Two  |
+--------+--------------------+------------+
I WANT to get the following result:
+--------+--------------------+----------------+
| res_id | res_name           | event_name     |
+--------+--------------------+----------------+
|      1 | TV/VCR             | Event One      |
|      2 | Overhead Projector | Event Two      |
|      3 | Table and Chairs   | NULL           |
|      4 | Artboard           | NULL           |
+--------+--------------------+----------------+
Any help would be greatly appreciated!

Thanks.

Chris Fowler
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]