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:


user_balance_transactions.regular_tournament_id is not NULL
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.
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"
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'