thinking about this more, you could probably do it even easier like so: SELECT eventID, eventName, MIN(eventDate) FROM event a INNER JOIN eventDates b ON a.eventID = b.eventID WHERE eventDate > #now()# GROUP BY eventID, eventName
that should bring back one record for each id. The only catch on this is that if there is no date greater than today for that event it will not show up. You would have to do a left outer join. On 11/16/05, Ryan Guill <[EMAIL PROTECTED]> wrote: > Oh yeah, if you want to limit it to one, you may want to do > max(eventDate) > #now()# or min(eventDate) > #now()# > > On 11/16/05, Ryan Guill <[EMAIL PROTECTED]> wrote: > > You almost had it: > > > > SELECT > > eventID, > > eventName, > > eventDate, > > FROM event a > > INNER JOIN eventDates b ON a.eventID = b.eventID AND eventDate > #now()# > > > > actually just learned this yesterday ;) > > > > On 11/16/05, Russ <[EMAIL PROTECTED]> wrote: > > > 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:224417 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
