>I have three tables - an Employee table, a Vegies table that holds the employee ID and their favorite vegetable and a Fruits table that holdes the employee ID and their favorite fruit.
>
>Employee 1 has a record in both Fruits and Vegies
>Employee 2 has a record in Fruits only
>Employee 3 has a record in Vegies only
>
>I have no problem with writing the SQL *sequential style* as follows:
>
>SELECT e.id, e.fname, e.lname, v.vegetable, f.fruit ;
> FROM employee e ;
> LEFT OUTER JOIN vegies v on e.id = v.id ;
> LEFT OUTER JOIN fruits f on e.id = f.id ;
>
>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
>
>What am I doing wrong?
>TIA

I don't know all the rules as well as I should but here's my .02 worth. I always try to keep the order of my "ON" lines the same as the order of my "JOIN" lines. SO I would have written your statement like:

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


I use the query build to write a lot of statements for me just because I'm lazy and I like it to do my typing for me. Plus half the time I can't remember exactly how my field names are spelled. I have noticed that the query builder will not always put the "ON" statements in the right order so I switch them around and it does seem to have a bearing on the results.