[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