Mysql – Cannot build correct SELECT in MySql

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"