Re: Complex query need help with OR condition.

 --- Ken Tozier <kentozier@comcast.net> escribió:
> I'm working on a query which works as expected when
> I leave out one of
> the "OR" tests but when the "OR" is included, I get
> hundreds of
> duplicate hits from a table that only contains 39
> items. Is there a way
> to write the following so that the "WHERE" clause
> tests for two
> possible conditions?
>
> Thanks for any help,
>
> Ken
>
>
> Here's the working query:
>
> SELECT a.paginator, a.doc_name, (b.time - a.time) as
> elapsed_time FROM
> pm_events as a, pm_events as b
>     WHERE a.event_code='pmcd'
>     AND b.event_code='pmcl'
>     AND a.doc_name=b.doc_name
>     AND a.paginator=b.paginator
>     AND a.time < b.time
>
> When I add the OR clause things go haywire:
>
> SELECT a.paginator, a.doc_name, (b.time -
> pm_events.time) as
> elapsed_time FROM pm_events as a, pm_events as b
>     WHERE a.event_code='pmcd'
>     OR a.event_code='pmop'
>     AND b.event_code='pmcl'
>     AND a.doc_name=b.doc_name
>     AND a.paginator=b.paginator
>     AND a.time < b.time
>
> Have also tried the following in the WHERE clause to
> no avail:
>
>     WHERE a.event_code IN {'pmcd', 'pmop'}
>     WHERE a.event_code=('pmcd' | 'pmop')
>
>


the query with the OR clause says:

SELECT a.paginator, a.doc_name, (b.time -
pm_events.time)
+++++++++

meanwhile the other one says:
SELECT a.paginator, a.doc_name, (b.time - a.time)
                                          +

Which pm_events table will the planner use a or b?? i
think in the second query you are confusing the
planner forcing a cartesian product.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com