Problems wit Left Joins and Keys

[Ritesh Sara]

Hi there,

I have 2 tables and 3 keys and want to process a LEFT JOIN:

Table1: all records should be displayed (LEFT JOIN)
Table2: only those values should be added where the 3 keys match

This query does not work at all:

SELECT Table1, Table2
FROM Table1 LEFT JOIN Table2 ON (

(Table1.Key1=Table2 Key1) AND (Table1.Key2=Table2 Key2) AND
(Table1.Key3=Table2 Key3))


Error message: The SQL statement could not be executed because it contains
ambigous outer joins. To force one of the joins to be performed first,
create a separate query that performs the first join and then include that
query in your SQL statement.

Could somebody assist here please?

Thanks much,

Rajesh

 

[Dirk Goldgar]

Hi there,

I have 2 tables and 3 keys and want to process a LEFT JOIN:

Table1: all records should be displayed (LEFT JOIN)
Table2: only those values should be added where the 3 keys match

This query does not work at all:

SELECT Table1, Table2
FROM Table1 LEFT JOIN Table2 ON (

(Table1.Key1=Table2 Key1) AND (Table1.Key2=Table2 Key2) AND
(Table1.Key3=Table2 Key3))


Error message: The SQL statement could not be executed because it
contains ambigous outer joins. To force one of the joins to be
performed first, create a separate query that performs the first join
and then include that query in your SQL statement.

Could somebody assist here please?

Thanks much,

Rajesh

Are you wanting to return all fields from both tables? Then you need
this:

SELECT Table1.*, Table2.*
FROM Table1 LEFT JOIN Table2 ON (
(Table1.Key1=Table2 Key1) AND (Table1.Key2=Table2 Key2) AND
(Table1.Key3=Table2 Key3))

 

[Ritesh Sara]

Hi Dirk,

Thanks for your response.

I tried this but it´s not working. Same error message.

Possible it´s an Access problem that Access can´t handle LEFT JOINS with 3
keys?

Is it working for you?

THanks much,
Rajesh

 

[Dirk Goldgar]

Hi Dirk,

Thanks for your response.

I tried this but it´s not working. Same error message.

Possible it´s an Access problem that Access can´t handle LEFT JOINS
with 3 keys?

Nope, it's not possible. I do it all the time.

The query you posted looks like a fudged example. Is that the exact SQL
you're trying to run? If not, please post the actual SQL, and we'll try
to figure out what's wrong.

 

[Albert D.Kallal]

Try using the query builder.

Drop in the main table, and then drop in the child table2.


now, draw the join line FROM table one to table 2 for the first key field.
Double click on that line, and choose the left join option.

Do this for the next 2 keys....

Your query now should work....