[thelist] Nested Left Outer Join in Access ?

matthew garrett matthew.garrett at snet.net
Thu Aug 16 12:01:34 CDT 2001


Hello SQL people... I *really* tried to not ask this...

I started with this query:

SELECT DISTINCT a, lot, of, fields
FROM hosts AS h , 
    w_strains AS w,
    host_strain_xref AS hs,
    cloned_gene AS c,
    host_strain_clone_xref AS hsc,
    researchers AS r
WHERE h.genus LIKE '*whatever*'
 AND h.host_id = hs.host_id
 AND h.host_id = hsc.host_id
 AND w.w_strain_id = hs.w_strain_id
 AND w.w_strain_id = hsc.w_strain_id
 AND c.clone_id = hsc.clone_id
 ;

Now, I want it to bring back *all* the hosts who's genus is 'whatever', not
just the ones where there is also a reference in the host_strain_xref table.

Since you can't do left outer joins like this [h.host_id *= hs.host_id] in
Access, I started re-writing with this syntax:

SELECT DISTINCT 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 INNER 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*';

So I try this, and there's no difference in the results (yes, i'm searching
for data which should reveal the difference):

SELECT DISTINCT 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*';

Any thoughts on what to try, or can I not get there from here?

Thanks,
matt





More information about the thelist mailing list