Three table join

[Guest]

I'd like to do a select with a join using three tables, but I can't seem to
do it. The query I'm trying to do should be something like:

SELECT a.task_id, a.task_start_date, a.task_finish_date, a.task_name,
a.task_pct_comp, a.task_is_summary, b.res_name
FROM msp_tasks a, msp_resources b msp_assignments c
WHERE a.task_name <> ''
AND a.task_uid = c.task_uid (+)
AND b.res_uid = c.res_uid
ORDER BY a.task_start_date;

However access doesn't seem to support the (+) syntax. So I poked around
and found this article:

http://office.microsoft.com/en-us/assistance/HA010345551033.aspx

Which suggests I might do something like:

SELECT a.task_id, a.task_start_date, a.task_finish_date, a.task_name,
a.task_pct_comp, a.task_is_summary, b.res_name
FROM msp_tasks a, msp_resources b LEFT JOIN msp_assignments c
ON a.task_uid = c.task_uid
WHERE a.task_name <> ''
AND b.res_uid = c.res_uid
ORDER BY a.task_start_date;

but I get syntax errors. What I'd like to do is to select what I've got
here (this is from a Microsoft Project database) but I want to include rows
in msp_tasks where no resource is specified (i.e. summary tasks). It appears
that I can't use the third table in the join.

Any help would be appreciated

Tom

 

[JohnFol]

Why not create a new query add in the three tables and drag/drop the fields
to create the join? This will generate the correct SQL.

 

[Guest]

Thanks for the reply, John. I tried this and it generated a query like:

SELECT *
FROM MSP_TASKS LEFT JOIN (MSP_ASSIGNMENTS INNER JOIN MSP_RESOURCES ON
MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID) ON MSP_TASKS.TASK_UID =
MSP_ASSIGNMENTS.TASK_UID;

(This is without all the extra stuff, to keep it simpler). However if I try
and save that query, I get the error "join expression not supported"

According to the help, there's two reasons for this:

1. Your SQL statement contains multiple joins in which the results of the
query can differ, depending on the order in which the joins are performed.
You may want to create a separate query to perform the first join, and then
include that query in your SQL statement.
2. The ON statement in your JOIN operation is incomplete or contains too
many tables. You may want to put your ON expression in a WHERE clause.

I'm definitely new to SQL. Can I do two selects in one statement? I think
that's what item 1 implies. I don't think item 2 is the case.

So what now? Thanks for the assistance.

Tom