Re: SQL Query

On Sat, Nov 30, 2002 at 02:05:20AM -0600, Scott Lamb wrote:
> On Sat, Nov 30, 2002 at 02:45:44AM -0500, Joel Burton wrote:
> > You can rewrite this w/EXISTS or with a multi-table join, and it
> > should perform better:
> >
> > SELECT t2.*
> > FROM trans AS t1,
> >   trans AS t2
> > WHERE
> >   t1.chart_id = 10074
> >   AND t1.trans_id = t2.trans_id
> >   AND t2.amount >= 0
> >   AND t2.transdate BETWEEN '2002-07-01' AND '2002-09-30'
>
> Doesn't that need a "distinct" to be equivalent to the exists query? If
> there are two 10074 rows with the same trans_id, I think all rows with
> that trans_id would be returned twice otherwise.

Good catch, Scott. Yes, if you have another row with trans_id=10088 and
chart_id=10074, this row and the original-correct row will both show up
twice.

Adding DISTINCT will prevent that, but it's not perfect -- this would
suppress the case where two matching rows were in the table, while this
would appear in the IN or EXISTS cases. Which may or may not be a
problem, depending on the application. Of course, the best solution to
this would be to ensure that the table has a primary key, even if its
just a SERIAL column. Then we could DISTINCT w/o fear.

Ok, did I miss anything else? ;)

- J.
--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant