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:
... and so on, when it should look like ...
from Users U
join Payment P
where U.Age > 30
= ( select min(PaymentExpirationDate)
where UserID = P.UserID
> '3/28/2004' )
by U.LastActive desc