Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    USA
    Posts
    3

    Red face Unanswered: Pulling data across multiple tables.

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if a user has more than one membership status record, which one do you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    USA
    Posts
    3
    I need the first membership status record that has a PaymentExpirationDate closest to '3/28/2004'.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select U.UserID 
         
    U.lastActive
         
    P.PaymentExpirationDate
      from Users U
    left 
      join Payment P
        on U
    .UserID 
         
    P.UserID 
       
    and P.PaymentExpirationDate 
         
    '3/28/2004'
     
    where U.Age 30 
       
    and P.PaymentExpirationDate 
         
    = ( select min(PaymentExpirationDate)
               
    from Payment
              where UserID 
    P.UserID
                
    and PaymentExpirationDate 
                  
    '3/28/2004' )
    order
        by U
    .LastActive desc 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •