List:General Discussion« Previous MessageNext Message »
From:Gerald L. Clark Date:October 16 2008 1:21pm
Subject:Re: Join question
View as plain text  
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
> 
try DISTINCT.

-- 
Gerald L. Clark
Sr. V.P. Development
Supplier Systems Corporation
Unix  since 1982
Linux since 1992
Thread
Join questionChris W16 Oct
  • Re: Join questionGerald L. Clark16 Oct
    • Re: Join questionChris W16 Oct