[thelist] Nested Left Outer Join in Access ?
matthew garrett
matthew.garrett at snet.net
Fri Aug 17 16:37:02 CDT 2001
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
More information about the thelist
mailing list