Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jun 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't select correct records, Need advice achieving desired query results

    Hi Fellas,

    I'm kindly seeking advice on achieving my desired query result. I have the following mysql query which I'm having difficulty with. I'm attempting to create a query to show me which of my users are delinquent more than 35 days from their last payment. I started with the following:


    Code:
    SELECT t. * , u. * , ut. * , e. * 
    FROM User_Payments AS t, Users AS u, Users_Training AS ut, Event AS e
    WHERE (
    t.User_Id = u.ID
    AND ut.User_Id = t.User_Id
    AND u.ID = ut.User_Id
    )
    AND (
    t.Event_Id = e.Event_Id
    AND ut.Event_Id = t.Event_Id
    AND ut.Event_Id = e.Event_Id
    )
    AND ut.Balance >0
    AND u.EQ7Day = 'YES'
    AND e.Event_CategoryId =22
    AND t.Payment_Date = (
    
    SELECT MAX(Payment_Date )
    FROM User_Payments
    WHERE User_Id = u.ID
    AND Event_Id = t.Event_Id
    )
    
    AND t.Payment_Date <= DATE_SUB( SYSDATE( ) , INTERVAL 35
    DAY )

    Now, without knowing my DB schema, etc, know that the query above works fine with one exception. The issue I've encountered is that many of my records have multiple t.Payment_Date which match their MAX(Payment_Date), for instance, if a user made multiple payments simultaneously and therefore has multiple payment records with the same exact t.Payment_Date (time stamp).

    Thus my query above outputs duplicate entries for users with duplicate t.Payment_Date matching their MAX(Payment_Date).

    To compensate for this, in an attempt to return only one result, I want to match additional criteria to narrow the query further. My logic was to also obtain the MAX(User_Payments_Id) among the records with the corresponding MAX(Payment_Date).

    I tried to add the following revision to the query but I didn't achieve my desired results. I'm kinda stuck now not sure exactly what I'm doing wrong or how to proceed.

    portion added...

    Code:
    AND t.User_Payments_Id = (
    
    SELECT User_Payments_Id
    FROM User_Payments
    WHERE User_Id = u.ID
    AND Event_Id = t.Event_Id
    ORDER BY MAX( Payment_Date )
    )

    full query with the addition...

    Code:
    SELECT t. * , u. * , ut. * , e. * 
    FROM User_Payments AS t, Users AS u, Users_Training AS ut, Event AS e
    WHERE (
    t.User_Id = u.ID
    AND ut.User_Id = t.User_Id
    AND u.ID = ut.User_Id
    )
    AND (
    t.Event_Id = e.Event_Id
    AND ut.Event_Id = t.Event_Id
    AND ut.Event_Id = e.Event_Id
    )
    AND ut.Balance >0
    AND u.EQ7Day = 'YES'
    AND e.Event_CategoryId =22
    AND t.Payment_Date = (
    
    SELECT MAX(Payment_Date )
    FROM User_Payments
    WHERE User_Id = u.ID
    AND Event_Id = t.Event_Id
    )
    
    AND t.User_Payments_Id = (
    
    SELECT User_Payments_Id
    FROM User_Payments
    WHERE User_Id = u.ID
    AND Event_Id = t.Event_Id
    ORDER BY MAX( Payment_Date )
    )
    
    AND t.Payment_Date <= DATE_SUB( SYSDATE( ) , INTERVAL 35
    DAY )
    I'm simply trying to limit the results to one distinct t.User_Payments_Id to avoid duplicates but I'm not exactly a mysql guru like you all. I sincerely appreciate any advice, suggestions, or points in the right direction that you can give this ole' gal. Thanks guys! XO

    Sierra

  • #2
    New to the CF scene
    Join Date
    Jun 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm a silly girl... I think I found my answer with a simple:

    Code:
    GROUP BY t.User_Id
    HAVING MAX(t.User_Payments_Id)
    Funny...

    Sierra XO

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    I think there's another way that might be more efficient. But it would depend upon your creating a SELECT of *only* the fields you needed, instead of using the sloppy * in the SELECT. Up to you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Jun 2003
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah, it's for simplicity for my own understanding and comfort level, simply due to my inexperience and lack of knowledge. I was initially considering capturing only the necessary fields and only selecting rows with distinct payment id and max payment date but wasn't exactly sure how to structure that query properly. Thanks for taking the time to reply, much appreciated!

    Sierra

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    The "trick" is to be able to use the combination of the date field and the ID so that are guaranteed to (a) get one record and (b) get one of the records with the most recent date.

    So the concept (not showing actual query, just joining two tables, but you'll get the idea) would be something like:

    Code:
    SELECT t.other, t.fields, u.also, u.these,
               MAX( CONCAT( CONVERT(t.payment_date,CHAR(10)), SUBSTRING(CONVERT(t.user_payment_id+1000000000,CHAR(10)),2) ) )
    FROM User_Payments AS t, Users AS u
    WHERE t.user_id u.user_id
    GROUP BY t.other, t.fields, u.also, u.these
    Do you see it? MySQL dates, when converted to string, come out as 2011/11/30 (10 characters). And then we get the user_payment_id and add 1,000,000,000 to it (assumes the payment id doesn't exceed 999,999,999), convert that to a string, and then lop off the first character (the 1).

    So suppose you had these records:
    Code:
    payment_date    payment_id     composite per code shown
    2011/11/29         1999            2011/11/29000001999
    2011/11/30          371            2011/11/30000000371
    2011/11/30          412            2011/11/30000000412
    And so the MAX() of that composite will, indeed select that last show record...just as you wanted.

    Now...you can't SELECT the payment_date or payment_id fields, else the GROUP BY will prevent finding the MAX you want. So you'd either need to use PHP (or whatever) code to extract the date and id out of the MAX value or you could JOIN a SELECT like this back to the main query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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