>The result has all three employees with a value for vegetable, fruit or both. My problem is trying to write it in ANSI style or trying to duplicate the above result in the Query Builder. I don't get the same result for Employee 3 who has a record in Vegies - the result for him is NULL in the vegetable column.
>
>SELECT Employee.*, Fruits.fruit, Vegies.vegetable;
> FROM employee LEFT OUTER JOIN fruits;
> LEFT OUTER JOIN vegies ;
> ON Fruits.id = Vegies.id ;
> ON Employee.id = Fruits.id
>

Try it like this:

SELECT Employee.*, Fruits.fruit, Vegies.vegetable;
FROM vegies;
RIGHT OUTER JOIN fruits;
RIGHT OUTER JOIN employee ;
ON Employee.id = Fruits.id
ON employee.id = vegies.id

To use the nested syntax here, you need the Employee table to be in the first join. Since I've listed it as the second table in that join, it's now a right join, not a left join. Remember that it's the order the tables are listed in that determines which kind of outer join you need, not the order of the expressions in the ON clause.

For the second join, you want to keep every record in the first join and add some records from Vegies, so again it's a right join. The ON clause needs to match Employee.Id with Vegies.Id because you want to match all employees with their vegetables, not fruits with vegetables.

Having said all that, I think the sequential syntax is a much better choice here. It makes much more sense to the reader and, in the long run, will be easier to maintain.

Tamar