From: Chris W
Date: October 16 2008 2:33pm
Subject: Re: Join question
List-Archive: http://lists.mysql.com/mysql/214910
Message-Id: <48F750CE.8030102@cox.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>>
> try DISTINCT.
>