That works as well... but it would require me to put all the other columns
that I'm selecting into the group by clause...
Wonder which solution is more efficient... this one or the one I came up
with:
Select eventID, eventName, eventDate
from event inner join eventDates on event.eventID=eventDates.eventID
where eventDateID IN (select top 1 eventDateID from eventDates where
eventDate>#Now()# and
eventDate.eventID=event.eventID
order by eventDate asc)
-----Original Message-----
From: Katz, Dov B (IT) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 16, 2005 5:32 PM
To: CF-Talk
Subject: RE: Query Question
How about this?
Select e.eventid, min(ed.eventdate) from events e inner join eventdates
ed on ed.eventid=e.eventid
Where ed.eventdate > getdate()
Group by eventid
-----Original Message-----
From: Russ [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 16, 2005 4:56 PM
To: CF-Talk
Subject: Query Question
I have 2 tables, for the sake of simplification lets say they look like
this:
EVENT
eventID
eventName
EVENTDATES
eventID
eventDate
Each event would be in the event table once, and possibly multiple times
in the eventDates table. I want to have a query return one record for
each event with the next eventDate from the eventDates table.
So basically something like:
Select eventID, eventName, eventDate from event inner join eventDates on
event.eventID=eventDates.eventID where eventDate>#Now()#
But I only want 1 record returned for each event. I know I can probably
order by eventID and then do a cfoutput groupby, but I don't really want
all those records coming back (I mean it will basically be number of
records in the event table * number of records in the eventDates table).
Is there a more efficient way of doing this?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:224416
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
