List:General Discussion« Previous MessageNext Message »
From:Chris W Date:October 16 2008 1:17pm
Subject:Join question
View as plain text  
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