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;