More than one JOIN in the Recordset

How do I write a Recordset that needs more than one JOIN?

Example:

This doesn't work. If it matters, I'm using Access 2000.

I get the error Microsoft JET Database Engine (0x80040E14) Operator missing. If I JOIN only two tables it works fine, but I need to JOIN 4 tables.

If somebody can bring some lights into this I will be very glad.

Hi EiSa,

There are 2 things you did wrong in your SQL Statement.

1) Always use Brackets around JOINS if there are more than one. The Database needs to know which JOIN to execute first

2) Be aware of ambigious outer joins.

Your SQL Statement should look something like this :

SELECT Table1.ThisID, Table2.ThatID, Table3.AnotherID
FROM (Table1 INNER JOIN Table2 ON Table1.ThisID = Table2.ThatID) INNER JOIN Table3 ON Table2.ThatID = Table3.AnotherID WHERE...

hth

Thank you for your reply, I'm a novice in JOIN. What do you mean with "Be aware of ambigious outer joins"? In this recordset I need to make an outer join because some values appears only sometimes (The plan was to hide/show a link depending on that value, something like If IsNull...).

I have dropped the important values from my recordset, please tell me if this is possible (and yeah, there are faults there in the JOIN'ing).


As I said, the OUTER JOIN is important and the only reasen that I try to write this recordset with the use of JOIN. Or is it another solution for my problem?

Thanx anyone

Hi,

I dont know exactly what you are looking for and also the schema of yr DB and tables so its very hard to comment.

However, I think there is something fundamentally flawed with your said SQL Statement. They are not joined properly.

Here is an example of a complex statement. Notice the nested bracket structures and also the operators.

********************
SELECT Table1.ThisID, Table2.ThatID, Table3.AnotherID, Table4.LastID
FROM ((Table1 INNER JOIN Table2 ON Table1.ThisID = Table2.ThatID) INNER JOIN Table3 ON (Table2.ThatID = Table3.AnotherID) AND (Table2.ThatID = Table3.AnotherID)) INNER JOIN Table4 ON (Table2.ThatID = Table4.LastID) AND (Table1.ThisID = Table4.LastID) AND (Table3.AnotherID = Table4.LastID);
**********************

Try using a wizard to generate some of these statements for you so you are more comfortable with JOINS and such. Then u can be on ur way to writing your own more complex joins.

However, please learn to keep JOINS to a minimum. They are complicated to maintain and are very processor-taxing.

You can also try to encapsulate some of these SQL Statements into a separate view or temporary table then link them up. The advantage to this is that they are more readable.

Good luck