List:General Discussion« Previous MessageNext Message »
From:Chris W Date:October 16 2008 2:33pm
Subject:Re: Join question
View as plain text  
I have no idea what I was thinking.  For some reason I was thinking 
Distinct wouldn't work, must have been temporarily brain dead.  Thanks 
for the wake up call.

Gerald L. Clark wrote:
> Chris W wrote:
>> I have two tables, one is a list of users and the other is a list of 
>> events for each user.  It is a one to many relationship.  The event 
>> table is pretty simple just an event type and a the date and time of 
>> the event in a datetime field.
>> I need a query that shows all events of a certain type for each user, 
>> very simple so far.  In fact the query I use now is simply,
>> SELECT u.FName, u.LName, e.EventType, DATE(e.DateTime)
>> FROM user u
>> JOIN event e USING(UserID)
>> ORDER BY u.LName, u.FName, e.EventType, e.DateTime
>> The twist comes in that there can be several records for a given user 
>> and event type all on the same day, in a case like that, I only want 
>> the query to show one record.  So I need one record per user per 
>> event type per day.  The query will strip the time part off of the 
>> date time field and only display the date.  We don't really care if 
>> that event happened 1 or 10 times in one day just that it happened at 
>> least once on that day for a user.
>> Chris W
Join questionChris W16 Oct
  • Re: Join questionGerald L. Clark16 Oct
    • Re: Join questionChris W16 Oct