I am trying to write a select statement with a right join (to clients), that will find a specific value in the join table - but ONLY if that is the most recent value for each client (ignoring blanks and nulls).
Clients Id Name 0 John Doe 1 Frank Smith 2 Sue Smith 3 John Smith Activity (join table) ClientId Type Date 0 500 2013-01-01 00:00:08 1 900 2013-01-01 00:00:07 2 NULL 2013-01-01 00:00:06 3 2013-01-01 00:00:05 4 500 2013-01-01 00:00:05 0 800 2013-01-01 00:00:04 1 500 2013-01-01 00:00:03 2 500 2013-01-01 00:00:02 3 500 2013-01-01 00:00:01 4 800 2013-01-01 00:00:00
So this query will at least give me only the client records that have an activity type of 500 (in this case I would get back client 0 and 4):
select * from clients right join activity on activity.clientid = clients.id where activity.type = 500
HOWEVER, I need to figure out how to make this return ONLY the first record in the above list of records. The logic there is Client #0 is the only client that has 500 as it's latest activity type = 500. The other 3 clients have NULL, blank, or 900 for example as their 'latest' activity type.
I am thinking some magic with ordering (the date would normally be pretty accurate), a 'top' and/or 'limit' and possibly union? Just cant quite wrap my head around it.
Please try this
SELECT activity.id AS activityid , activity.type , activity.date , clients.id AS clientid , clients.name FROM activity LEFT JOIN activity AS other_activities ON activity.ClientID = other_activities.ClientID AND activity.date < other_activities.date LEFT JOIN clients ON activity.ClientID = clients.id WHERE activity.type = 500 AND other_activities.ClientID IS NULL;