[thelist] Nested Left Outer Join in Access ?

[matthew garrett]

Thanks Rudy,
I got that far, but I was having trouble with the left join syntax (or some
aspect of it). Here's more-or-less what I'm working with - the left join is
in the inner-most sanctum of the from clause:

SELECT a, lot, of, fields
FROM cloned_gene AS c INNER JOIN
    (w_strains AS w INNER JOIN
    (host_strain_clone_xref AS hsc INNER JOIN
    (hosts AS h LEFT JOIN host_strain_xref AS hs
    ON h.host_id = hs.host_id) ON hsc.host_id = h.host_id)
    ON w.w_strain_id = hsc.w_strain_id)
    ON c.clone_id = hsc.clone_id
WHERE h.genus LIKE '*whatever*';

This brings back 9 records (all of which are referenced in the
host_strain_xref table) even though there are 18 host records where genus =
'mysearchcriteria'. If I could have had those non-referenced records back I
would have had a much more enjoyable August.

I'm not used to writing joins this way (and barely used to writing them
*any* way) - but it's *not* giving me an error in Access.

Any further hints?

matt
 
> dunno what happened, i wrote this last night but only just now noticed it
> sitting in my Drafts folder unsent...

yup, been there...

> hope it is still helpful
> 
> 
>> Since you can't do left outer joins like this [h.host_id *= hs.host_id]
>> in Access
> 
> yeah, that syntax is not recognized
> 
> use LEFT JOIN instead
> 
> note:  you can nest LEFT JOIN in an INNER JOIN but not the other way round