pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: M. Scott Smith <mssmit1@afterlife.ncsc.mil>
To: pgsql-sql@postgresql.org
Subject: Problem with limit / union / etc.
Date: Tue, 28 Dec 1999 11:17:43 -0500
Message-ID: <199912281615.LAA13003@afterlife.ncsc.mil> (raw)

Hi!

   It's been awhile since I've done SQL, and I'm having a problem with
a query.  I thought I had this working but can't figure out what's
wrong now.  What I'm trying to do is pull a list of events from a
database, and limit the number of events returned.  The query is
a little complicated because three tables are involved:

create table event
(
  title               varchar(256) not null,
  date_start    date not null
);

create table url
(
  title...
);

create table event_url
(
  eventid   OID,
  urlid        OID
);

   An event may (theoretically) have zero or more URL's associated
with it; which is why the url isn't stored directly in the event table.
The event_url table relates URL(s) with an event.

   What I want to do is pull out upcoming events, limiting the number
returned.  The following query is my attempt to do that.  The
first select selects all events that have a URL associated with
them; the second query selects all events that DON'T have a URL
associated with them; the results are combined together.  My
hope is that the order by/limit clause will limit the results returned
after they are combined, but it seems to be ignoring this.  I've tried
placing the order by in other places or multiple places to no avail.

   Should this be working?  Am I doing something stupid?  Any
help would be greatly appreciated!  I suppose I could output the
results of this query into a temporary table and then select all
from that table with limit, but it doesn't seem like that should
be necessary.  (Separately, are there better ways to reflect
"one or more" entities in a table (such as URLs) without resorting
to multiple tables?)

Thanks!

- Scott

select
  event.title
from
  event, event_url, url
where
  date_start >= 'now'::date
  and event.oid = event_url.eventid
  and url.oid = event_url.urlid

union

select
  event.title
from
  event,event_url
where
  date_start >= 'now'::date
  and event.oid not in
   (select distinct event_url.eventid from event_url)

order by event.date_start asc limit $total;



view thread (3+ messages)  latest in thread

Message-ID: <199912281615.LAA13003@afterlife.ncsc.mil>
Permalink:  ../199912281615.LAA13003@afterlife.ncsc.mil/
Also on:    postgresql.org/message-id/199912281615.LAA13003@afterlife.ncsc.mil

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mssmit1@afterlife.ncsc.mil
  Subject: Re: Problem with limit / union / etc.
  In-Reply-To: <199912281615.LAA13003@afterlife.ncsc.mil>
  References: <199912281615.LAA13003@afterlife.ncsc.mil>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox