Re: Complex Query

I didn't really examine your query in depth, however here is a very important rule to know:

To avoid cartesian products, you must have a minimum of n-1 predicate clauses in your query, where "n" is the number of tables you are joining.

For example, if you have:

SELECT id, name, dept, city FROM
t1, t2, t3, t4

you need at least three of these:

WHERE t1.id = t2.id
AND t2.name = t3.name
AND t3.dept = t4.dept;

I noticed that your query does not meet this rule.

Hope that helps!

In article <8fv4dk$669$1_at_nnrp1.deja.com>,   medavarm_at_usa.redcross.org wrote:
> Hi,
>
> I got this query:
>
> select d.id, d.site, d.spon, d.total_presenting, r.dtype,
 rr.codabarwbn
> wbnstart,rx.codabarwbn wbnend
> from drive d, regis r, uniquewbn rr, uniquewbn rx
> where trunc(d.ddate)='26-APR-00'
> and d.id = r.drv
> and r.id = rr.donatkey
> and (rr.codabarwbn, rx.codabarwbn) in
> (select min(uniquewbn.codabarwbn), max(uniquewbn.codabarwbn)
> from regis,uniquewbn
> where d.id = regis.drv and regis.id = uniquewbn.donatkey)
> order by d.id
>
> and I get these results:
>
> ID SITE SPON TOTAL_PRESENTING D WBNSTAR WBNEND
> ---------- ---------- ---------- ---------------- - ------- -------
> 14968 1051 852 35 F 89770 F 89801
> 14968 1051 852 35 F 89770 F 89801
>
> Why am I getting duplicate rows?
> I know there is a join missing but how do I do that.
> Can somebody modify this query to avoid the duplicate rows.
>
> Thank you,
> Moorthy
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 17 2000 - 00:00:00 CDT