Re: joins: avoiding recurrences

Torsten wrote:
> #1:
> SELECT sw_id,
> sw_date,
> acw_li,
> acw_li_err,
> axw_atrazine,
> axw_atrazine_err,
> an.an_analysis_id,
>
> FROM an_chemistry_water,
> an_tracer_water,
> analyses an,
> samples_water,
> (SELECT axw_atrazine, axw_atrazine_err, an_analysis_id, an_pid
> FROM an_xenobiotics_water,
> analyses,
> samples_water
> WHERE (axw_fid_an = an_pid)
> AND (an_fid_sw = sw_pid))
>
> WHERE (acw_fid_an(+) = an.an_pid)
> AND (an.an_fid_sw = sw_pid)
> AND (an.an_pid NOT IN
> (SELECT an_pid FROM analyses, an_tracer_water
> WHERE an_pid = atw_fid_an));

When joining n tables, you need n-1 join conditions... You are joining 5 tables (4 tables and one inline view), so youd should have at least 4 join coinditons. I can only see 2... You need something more in the where clause!

This is of course unless you intended to create a cartesian product between some of the tables.

Andreas Received on Tue Feb 24 2004 - 11:12:32 CST