>>>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.
>
>
>Rip,
>
>Thanks for your response. I tried your way but the result was now NULL for all employees in both veg and fruit fields.

What about this one

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