I am attempting to pull all users over the age of 30 from a table. I want the results to be returned off of their membership status and recent activity. Their membership status is stored in a seperate table (Payment). Some users have more than one membership status record, while others might not have any at all.

LEFT JOIN seems like a possible solution, however, if a user has more than one membership status record, it is returning multiple results for that user (when I only want 1 result for that user).

Here is the code that I have so far.

SELECT Users.UserID FROM Users LEFT JOIN Payment ON Users.UserID = Payment.UserID AND Users.Age > 30 AND Payment.PaymentExpirationDate > '3/28/2004' ORDER BY Payment.MembershipNumber DESC, Users.LastActive DESC

Payment.MembershipNumber - INT Field. This is the membership status for the user, if the user has any membership records.
Users.LastActive - Date/Time Field. This is the recent active for the user.

The data is being returned right now is:
UserID
=====
3
8
12
12
12
9
9
1
... and so on, when it should look like ...
UserID
=====
3
8
12
9
1

I hope this makes sense. Thanks for your time.

James

if a user has more than one membership status record, which one do you want?

I need the first membership status record that has a PaymentExpirationDate closest to '3/28/2004'.