>>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.