2

Please help me to create a correct MySql query, which I failed to do.

I have 2 tables in our DB:

CREATE TABLE IF NOT EXISTS `user_audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `audit_event` varchar(128) NOT NULL,
  `data` varchar(256) NOT NULL DEFAULT '',
  `source_ip` varchar(256) NOT NULL DEFAULT '',
  `user_agent` varchar(256) NOT NULL,
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27181 ;

CREATE TABLE IF NOT EXISTS `user_balance_transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `audit_event_id` int(10) unsigned NOT NULL,
  `private_tournament_id` int(10) unsigned DEFAULT NULL,
  `regular_tournament_id` int(10) unsigned DEFAULT NULL,
  `points` int(11) NOT NULL,
  `description` varchar(256) NOT NULL DEFAULT '',
  `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `private_tournament_id` (`private_tournament_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1001 ;

I have 2 audit events that I'm interested in:

user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_PAID"
user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_REFUNDED"

These audit events are opposite to each other, meaning user can pay, then can be refunded, then use pays again and so on.

On top of these 2 table I have to build one SQL select statement that will return DISTINCT user_id, regular_tournament_id, create_timestamp of ALL USERS with:

  1. user_balance_transactions.regular_tournament_id is not NULL

  2. user_balance_transactions.audit_event_id is pointing to user_audit table where user_audit.audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID' and it's the last event (most important!) that happened chronologically.

    IMPORTANT: In user_audit table 2 possible audit events are related to each other: 'RGL_TOURNAMENT_ENTRANCE_PAID' and 'RGL_TOURNAMENT_ENTRANCE_REFUNDED', but we need user_id only of such users who has 'RGL_TOURNAMENT_ENTRANCE_PAID' event as last.

  3. create_timestamp is a timestamp of this last event - 'RGL_TOURNAMENT_ENTRANCE_PAID'


A. Test data for "user_balance_transactions" table:

id, user_ID, audit_event_id, private_tournament_id, regular_tournament_id, points, description, create_timestamp

2, 23, 1711, null, 77, 10, "credit", "2015-06-12T17:23:44"

3, 23, 1712, null, 77, -10, "debit", "2015-06-12T17:41:44"

4, 23, 1713, null, 77, 10, "credit", "2015-06-12T18:11:44"

B. Test data for "user_audit" table:

id, user_ID, audit_event, data, source_ip, user_agent, create_timestamp

1711, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T17:23:44"

1712, 23, "RGL_TOURNAMENT_ENTRANCE_REFUNDED", "","","", "2015-06-12T17:41:44"

1713, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T18:11:44"

C. Expected SQL output:

user_id, regular_tournament_id, create_timestamp

23, 77, "2015-06-12T18:11:44" (last RGL_TOURNAMENT_ENTRANCE_PAID event)

"user_balance_transactions" stores user transaction and "user_audit" stores all user audit events, of which some of them related to the balance transactions.

That's why "user_balance_transactions" table has a Foreign Key to "user_audit" table "audit_event_id"

1
  • Can you add your expected output and some test values? Help us to help you.
    – oNare
    Jul 20, 2015 at 14:39

2 Answers 2

1

Try this:

SELECT 
       t.user_id, 
       t.audit_event 
FROM
        (SELECT ua.user_id, ua.audit_event FROM user_audit ua, user_balance_transactions ubt WHERE
        (audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID' OR audit_event = 'RGL_TOURNAMENT_ENTRANCE_REFUNDED')
        AND ubt.regular_tournament_id='{$tournament_id}'
        AND ubt.audit_event_id=ua.id
        ORDER BY ua.create_timestamp DESC LIMIT 1) t
        WHERE t.audit_event != 'RGL_TOURNAMENT_ENTRANCE_REFUNDED'
1
  • I tried and it does not work. Always returns just one user instead the list of distinctive users.
    – markovski
    Jul 20, 2015 at 16:31
0

You can use MAX function to get the max value from create_time of user_balance_transactions table.

SELECT
    ua.user_id,
    ubt.regular_tournament_id,
    MAX(ubt.create_timestamp) as event_date,
    ua.audit_event
FROM test.user_audit AS ua
LEFT JOIN test.user_balance_transactions AS ubt ON (ubt.audit_event_id=ua.id AND ubt.user_id=ua.user_id AND ua.audit_event='RGL_TOURNAMENT_ENTRANCE_PAID')
GROUP BY ua.user_id;

UPDATE:

Test:

mysql> SELECT
    ->     ua.user_id,
    ->     ubt.regular_tournament_id,
    ->     MAX(ubt.create_timestamp) as event_date,
    ->     ua.audit_event
    -> FROM test.user_audit AS ua
    -> LEFT JOIN test.user_balance_transactions AS ubt ON (ubt.audit_event_id=ua.id AND ubt.user_id=ua.user_id AND ua.audit_event='RGL_TOURNAMENT_ENTRANCE_PAID')
    -> GROUP BY ua.user_id;
+---------+-----------------------+---------------------+------------------------------+
| user_id | regular_tournament_id | event_date          | audit_event                  |
+---------+-----------------------+---------------------+------------------------------+
|      23 |                    77 | 2015-06-12 18:11:44 | RGL_TOURNAMENT_ENTRANCE_PAID |
+---------+-----------------------+---------------------+------------------------------+
1 row in set (0.00 sec)

mysql> 
2
  • Sorry, didn't work
    – markovski
    Jul 20, 2015 at 19:06
  • It's working as you see on the update, but, what's not working for you?
    – oNare
    Jul 20, 2015 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.