davedpss Posted November 22, 2011 Share Posted November 22, 2011 I have a user table with one record per user and a membership table with a record for each user for each year they have joined. I need a query that will find each user that is not a current member (mstatus = Non-Member) and only the latest record for that user in the membership table, if there are any. I can't figure out how to filter the joined table (membership) to only get the latest record.Select * from user Left Join membership on user.id = membership.userid;This gives me all the records for membership and I only need the latest. MySQL 4.0.24.Thanks - Dave. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 22, 2011 Share Posted November 22, 2011 Order the records by a date field or whatever you use to determine which is the latest, and use LIMIT to only return the first one. Link to comment Share on other sites More sharing options...
davedpss Posted November 22, 2011 Author Share Posted November 22, 2011 Nope, that won't work. I need all the user records and only one membership record per user. Limit 1 returns only one user. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 22, 2011 Share Posted November 22, 2011 I misunderstood. You'll probably have to use a temporary table with the join, where the table you join with is a select statement that gets the users and the max or min value of the field you use to determine which is the latest, grouped by the user. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.