Complex query need help with OR condition.

From: Ken Tozier <kentozier(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Complex query need help with OR condition.
Date: 2004-10-25 04:06:48
Message-ID: 4A938880-263B-11D9-87A4-003065F300E2@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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')

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-10-25 04:35:10 Re: Complex query need help with OR condition.
Previous Message Chris 2004-10-25 04:05:01 Re: partial index on a text field