SQL LEFT JOIN strange syntax error?

Question

===problem===

i'm using a LEFT JOIN, SQL expression, on 3 tables. i'm getting an unexpected error "JOIN expression not supported" from MS ACCESS 2007 when i try to run it.

===details===

these tables are all connected

• parent: is at the highest level
• child1: child of parent
• child2: child of parent
• grandchild1: child of child1

this is the SQL expression causing the error:

SELECT *
FROM ((grandchild1 AS gc
      LEFT JOIN child1 AS c1 ON gc.child1_id=c1.id)
      LEFT JOIN parent AS p ON c1.parent_id=p.id)
      LEFT JOIN child2 AS c2 ON (p.id=c2.parent_id 
                                 AND c2.start<=gc.time AND gc.time<=c2.stop)

strangely, the following expression in which i've only replaced one of the Boolean expressions with TRUE in the "ON" clause does get accepted:

SELECT *
FROM ((grandchild1 AS gc
      LEFT JOIN child1 AS c1 ON gc.child1_id=c1.id)
      LEFT JOIN parent AS p ON c1.parent_id=p.id)
      LEFT JOIN child2 AS c2 ON (TRUE 
                                 AND c2.start<=gc.time AND gc.time<=c2.stop)

===questions===

• is there something wrong with the syntax of my expression?
• another things i have noticed is that i can't use an EXISTS clause inside the ON clause, is that normal?

===solution=== (thanks David-W-Fenton)

SELECT *
FROM ((grandchild1 AS gc
      INNER JOIN child1 AS c1 ON gc.child1_id=c1.id)
      INNER JOIN parent AS p ON c1.parent_id=p.id)
      INNER JOIN child2 AS c2 ON (p.id=c2.parent_id)
                               AND (c2.start<=gc.time) AND (gc.time<=c2.stop)

Answer 1

Normally you write a join like this:

SELECT * FROM grandchild1 AS gc
LEFT JOIN child1 AS c1 ON gc.child1_id=c1.id
LEFT JOIN parent AS p ON c1.parent_id=p.id
LEFT JOIN child2 AS c2 ON (p.id=c2.parent_id AND c2.start<=gc.time AND gc.time<=c2.stop)

(Note the missing parens.)

Regarding EXISTS, perhaps that's an Access limitation? MySQL's happy enough with having an EXISTS in an ON clause.

Answer 2

Take a look at the reference documentation

It looks somewhat specific, to having only a single operation allowed in the ON clause. Also, it looks like for access 2007, you're going to have to nest your joins.
Additionally, I think that date range should be in a WHERE clause.

Try this:

SELECT *
FROM grandchild1 AS gc
LEFT JOIN (

  child1 as C1 LEFT JOIN (

      parent as P LEFT JOIN child2 as C2 ON P.id = C2.parent_id 

  ) ON c1.parent_id = P.id

) ON gc.child1_id = C1.id

WHERE 
    c2.start <= gc.time AND gc.time <= c2.stop

Answer 3

In your non-equi join, you must have the tables in the same order. Instead of this:

  c2.start<=gc.time AND gc.time<=c2.stop

...you need this:

  c2.start<=gc.time AND c2.stop>=gc.time

...or:

  gc.time>=c2.start AND gc.time<=c2.stop

You might also test to see if BETWEEN works:

  gc.time BETWEEN c2.start AND c2.stop

BETWEEN is inclusive on both sides, so I think it's exactly equivalent to your original criteria.

All that said, I think the problem is that you're defining a join with three conditions, one of which applies to one pair of tables, and the other two of which apply to a different pair of tables. Your first condition, p.id=c2.parent_id, joins c2 to p, while your second pair of non-equi conditions joins c2 and gc. These kinds of joins are tricky.

I would suggest using the Access QBE to define your joins as equi-joins, and then adjusting the SQL of the join to make it a non-equi join.

Alternatively, it might just be simpler to apply the date/time criteria in the WHERE clause, i.e., as an implicit join.

Answer 4

use a WHERE condition and leave the ON conditions for the purpose they were intended namely specifying how tables are to be joined not for filtering data. Your method is also much harder to read and doesnt always work for instance move the WHERE condition of method 3 to the ON clause and see for yourself.

drop table if exists t1;
create table t1(id int unsigned not null primary key);

drop table if exists t2;
create table t2(id int unsigned not null primary key);

insert into t1 (id) values (1),(2),(3),(5),(4),(6);
insert into t2 (id) values (2),(4),(6);

-- method 1:
select t1.id from t1 where id not in (select id from t2);

-- method 2:
select t1.id from t1 where not exists (select id from t2 where t1.id = t2.id);

-- method 3:
select
 t1.id
from
 t1
left outer join t2 on t1.id = t2.id
where
 t2.id is null;