...

View Full Version : Can't select correct records, Need advice achieving desired query results



sierra
11-30-2011, 09:37 PM
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:




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...



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...


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

sierra
11-30-2011, 10:15 PM
I'm a silly girl... I think I found my answer with a simple:



GROUP BY t.User_Id
HAVING MAX(t.User_Payments_Id)


Funny...

Sierra XO

Old Pedant
12-01-2011, 01:00 AM
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.

sierra
12-01-2011, 05:45 AM
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

Old Pedant
12-01-2011, 06:21 AM
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:



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:


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum