Need help with complex MySQL SELECT

Hi,

We have 2 Tables:
Clients Table
Payments Table which has record of every payment transaction for a given client

I need to generate a list which shows all clients and the last transaction for that client Grouped by client so that a client is listed once only and VERY IMPORTANT sorted in such way that for each client listing their last Payment record is listed for them. So the list should show the clients sorted based on the date of their last transaction in ASC order for all clients.

I have tried this MySQL select but it is producing bad results since it is not listing the last date of Payment transaction for a given client and rather than listing the clients sorted in ASC order based on the date of their last payment it is sorting them based on their User_id!

SELECT payments.id, payments.user_id, clients.company, payments.payment_added, clients.credit_due, payments.declined_payments,
FROM clients, payments
WHERE clients.id = payments.user_id
AND user_status = 2 AND vs_result = 0 AND clients.advertising_amount > 0
GROUP BY pre_advert_payments.user_id
ORDER BY pre_advert_payments.payment_added ASC;

What to do to get correct results?
ThanX,

i’m surprised it ran at all, it has several syntax errors :slight_smile:

try this –

SELECT payments.id
     , payments.user_id
     , clients.company
     , payments.payment_added
     , clients.credit_due
     , payments.declined_payments
  FROM clients
INNER
  JOIN ( SELECT user_id
              , MAX(payment_added) AS latest
           FROM payments
         GROUP
             BY user_id ) AS p
    ON p.user_id = clients.id
INNER
  JOIN payments
    ON payments.user_id = clients.id
   AND payments.payment_added = p.latest
 WHERE clients.user_status = 2 
   AND clients.vs_result = 0 
   AND clients.advertising_amount > 0
ORDER 
    BY payments.payment_added ASC;

Hi,

I did what you suggested but the results are all wrong, that is first big error is that clients, which means their user_ids, are appearing multiple times in the list rather than ONLY once based on their latest transaction (payment).

FYI, here is the actual code:

SELECT pre_advert_payments.id AS pay_id, anoox.pre_advert_payments.user_id, pre_advert_users.company
AS customer, pre_advert_payments.payment_added, rebill_amount, credit_due, pre_advert_payments.declined_payments,
cancel_rebill FROM anoox.pre_advert_users
INNER
 JOIN (SELECT anoox.pre_advert_payments.user_id, MAX(payment_added) AS latest FROM anoox.pre_advert_payments
       GROUP BY anoox.pre_advert_payments.user_id) AS p
       ON p.user_id = anoox.pre_advert_users.id
INNER
 JOIN anoox.pre_advert_payments
 ON anoox.pre_advert_payments.payment_added = p.latest
WHERE anoox.pre_advert_users.user_status = 2 AND vs_result = 0 AND pre_advert_users.advertising_amount > 0
AND pre_advert_payments.declined_payments <= 3
ORDER BY pre_advert_payments.payment_added ASC;

you forgot one of the join conditions

that happens often when you change table/column names for the forum, get a solution, and then have to transcribe the solution back to your original table/column names

:slight_smile:

SELECT p.id AS pay_id
     , p.user_id
     , u.company AS customer
     , p.payment_added
     , rebill_amount
     , credit_due
     , p.declined_payments
     , cancel_rebill 
  FROM anoox.pre_advert_users AS u
INNER
  JOIN ( SELECT user_id
              , MAX(payment_added) AS latest 
           FROM anoox.pre_advert_payments
          WHERE declined_payments <= 3
         GROUP 
             BY user_id ) AS m
       ON m.user_id = u.id
INNER
  JOIN anoox.pre_advert_payments AS p
    ON [COLOR="Blue"]p.user_id = u.id[/COLOR]
   AND p.payment_added = m.latest
 WHERE u.user_status = 2 
   AND u.vs_result = 0 
   AND u.advertising_amount > 0
ORDER 
    BY p.payment_added ASC

Hi,

1st, the code below gave Error message that:
Unknown column ‘u.vs_result’ in ‘where clause’

well since vs_result is in the pre_advert_payments table, so I changed that
to: p.vs_result

But then the results are all wrong since again it is displaying a client multiple number of time for all their transactions and not just for their latest transaction.

ThanX :slight_smile:

could you show a few rows of actual output please

oh, and since (after 2 attempts) i finally got you to put the right table prefix on the vs_result column, i think you should add that condition to the subquery as well

Hi,

I am not sure how I can show you few rows since they are very long.

And not sure what you mean by the last comment.

Let me ask you a simpler question:

if I want to generate a list of all companies (user_id) from one table, the payments table, and wanted each row for a given user_id to be the one with their latest entry (transaction date), how would I do that?

Something like this effect:

SELECT * FROM payments
GROUP BY user_id WHERE MAX(payment_added);

Regards,

i am not sure why you are abandoning the query we’ve been working on

but to answer your latest question, you would do it like this –

SELECT payments.user_id
     , payments.payment_added
     , payments.foo
     , payments.bar 
  FROM ( SELECT user_id
              , MAX(payment_added) AS latest
           FROM payments
         GROUP
             BY user_id ) AS m
INNER
  JOIN payments
    ON payments.user_id = m.user_id
   AND payments.payment_added = m.latest

look familiar? :slight_smile:

Hi,

Well I Have been working on this all weekend and still not got it 100% :frowning:

So 1st, the code that you have provided below, does not run at all, since one gets an error message that pre_advert_users does not exist.

I tried one of my own codes with some of your suggestion, and it is almost correct, but not 100% correct. That is this code:

SELECT pre_advert_payments.id AS pay_id, user_id, pre_advert_users.company, pre_advert_payments.payment_added, MAX(payment_added) AS latest, advertising_amount AS balance, pre_advert_payments.declined_payments AS decline
FROM anoox.pre_advert_users, anoox.pre_advert_payments
WHERE pre_advert_users.id = pre_advert_payments.user_id
AND user_status = 2 AND unlimited_click != 'yes' AND payment_type IN (0, 1, 3) AND advertising_amount > 0
GROUP BY user_id ORDER BY latest ASC;

The above code does not actually produce the latest record for a given user_id transaction. That is for example the pay_id that it produces is not the pay_id of the latest transaction record for a given client (user_id). However it does produce the date of last transaction for a given client (user_id) correctly and does list one client transaction record only, ect.

So can you tell me what I need to do with my code to correct this last remaining glitch? Since I find your codes too complex :slight_smile: and your codes are not running and I cannot debug them because they are so complex I would appreciate if you could suggest what I need to do with my code to get the Job done?

ThanX,

P.S. I hope it is not too cold up in Toronto :slight_smile:

The only reason I can see that r937 query would generate duplicate transactions per a client would be transactions share the same exact time. In that case there is an issue. The only way to rectify it would be to just pick one ambiguously using a group by clients id on the outer most query.

Could you post the create table for the tables in question.

This will come to light with the schemas but the payment_added data type could be an issue. That is if its not some form of date or timestamp max() will not generate the intended outcome.

The other possible issue could be that pre_advert_users has duplicate users. I think by naming the table “clients” intitialy it has been assumed the table represents all the unique clients – is that incorrect?

None the less, a great place to start debugging subqueries is to start at the inner most and work your way up. If the below query results in the most recent transaction per user than the issue lies with the outer query. If it does not than its an issue with the subquery to start.


SELECT user_id
              , MAX(payment_added) AS latest
           FROM payments
         GROUP
             BY user_id 

What you should be confirming with that query is the users most recent transaction date. If it does not result in that than its most likely something to do with the payment_added data type, not being treated as a date or time.

once again, you’ve introduced new columns to the query without qualifying them by table name, forcing me to guess which table they’re in…

also, this is the last time i show you how to join to the latest row produced by a subquery…


SELECT p.id AS pay_id
     , p.user_id
     , u.company
     , p.payment_added
     , p.advertising_amount AS balance
     , p.declined_payments AS decline
  FROM anoox.pre_advert_users AS u
INNER
  JOIN ( SELECT user_id
              , MAX(payment_added) AS latest
           FROM anoox.pre_advert_payments 
          WHERE payment_type IN (0,1,3) 
            AND advertising_amount > 0
         GROUP
             BY user_id ) AS m
    ON m.user_id = u.id
INNER
  JOIN anoox.pre_advert_payments AS p
    ON p.user_id = u.id 
   AND p.payment_added = m.latest
 WHERE u.user_status = 2 
   AND u.unlimited_click <> 'yes' 
   AND p.payment_type IN (0,1,3) 
   AND p.advertising_amount > 0
GROUP 
    BY p.user_id 
ORDER 
    BY p.payment_added ASC;

Hi,

FYI, I already tried this simpler code:


SELECT user_id, MAX(payment_added) AS latest
           FROM pre_advert_payments
         GROUP
             BY user_id
             ORDER BY latest ASC;

And this produces the correct data.
But once I incorporate this into the complex code, the one that incorporates both tables then it produces in-correct results. WTS, see my other replies interspersed:

Hi,

Your code is producing multiple error messages, such as:

Unknown table ‘p’ in where clause

So if you can answer my specific question that would be great.
Otherwise, dont worry about it. Because your code as you are sending them over are just to convoluted for me to make sense out of them :slight_smile:

what question?

Hi,

My question is that this code:

SELECT pre_advert_payments.id AS pay_id, user_id, pre_advert_users.company, pre_advert_payments.payment_added,
MAX(payment_added) AS latest, advertising_amount AS balance, pre_advert_payments.declined_payments AS decline
FROM pre_advert_users, pre_advert_payments
WHERE pre_advert_users.id = pre_advert_payments.user_id
AND user_status = 2 AND unlimited_click != 'yes' AND payment_type IN (0, 1, 3) AND advertising_amount > 0
GROUP BY user_id ORDER BY latest ASC;

Produces almost the results I want, but with one Error.
That is it does work correctly in these Key regards:
1- It does produce one client (user_id) row per client only
2- It does show the date of their last transaction, which is: MAX(payment_added) AS latest,

However it does not work in regard:
That is the row that it is producing per client is not actually their last transaction (based on date) row from the pre_advert_payments Table, that is for example the value of the pay_id does not belong to the same row where the value of MAX(payment_added) AS latest was obtained from which is the row that I need.

So how do I change above code, so that it produces the values that are in the row where the MAX(payment_added) AS latest was obtained from?

ThanX,

i have already shown you – multiple times – how to do it

And I told you multiple times that your code does not run.
That is produced MySQL Errors.

For example after running the latest code you provided, it produces Error:

Unknown column ‘advertising_amount’ in ‘where clause’

if you want me to continue helping you, please do the following

  1. do a SHOW CREATE TABLE for each table

  2. show the exact query you ran, along with the exact error message

by the way, the reason you got “Unknown column ‘advertising_amount’ in ‘where clause’” is because obviously that column isn’t in the payments table

i guess you missed the muiltiple times where i tried to get you to indicate which table these columns are all in

see my earlier post where i said i had to guess? so, if i guessed wrong, why didn’t you fix it?

Hi,

For security reasons I cannot show you the Create here in public.
I can email it to you.

About the 2nd q, I ran your last query exactly as you had it on your message.
And it produced the Error message that I typed in, which is:

Unknown column ‘advertising_amount’ in ‘where clause’

Regards,

For security reasons I cannot show you the Create here in public.

doh!

IMV there’s not much to be given away in a show create table statement that you haven;t alreayd given away in your OP.

I know I’m hyper-sensitive when it comes to divulging stuff about my ‘system’ so I can relate to your concerns but, erm, it won’t contain any data and, as one table of a whole db, it’s unlikely to give away any secretsthat you haven’t already revealed?

and, if you need help, it’s very hard to do it privately in a public forum.