48NpWds0

1CREATE TABLE IF NOT EXISTS `User_Table` (
2    `user_table_id`  int unsigned        not null auto_increment,
3    `user_id`        int unsigned        not null,
4    `unixtimes`      int unsigned        not null,
5    `status`         char(1)             not null default '',
6    `text1`          text                not null,
7    `text2`          text                not null,
8PRIMARY KEY (`user_table_id`),
9UNIQUE INDEX user_table_1 (`user_id`, `status`, `unixtimes`)
10); 
11     
12| user_id | login | pending_text1 | pending_text2 | current_text1 | current_text1 |
13|---------|-------|---------------|---------------|---------------|---------------|
14| 8675309 | Bob   | First try     | First other   | Second try    | Second other  | 
15     
16SELECT
17    `up`.`user_id` AS 'user_id',
18    `u`.`login` AS 'login',
19    `up`.`text1` AS 'pending_text1',
20    `up`.`text2` AS 'pending_text2',
21    `record_current`.`text1` AS 'current_text1',
22    `record_current`.`text2` AS 'current_text2'
23
24FROM
25    `user_table` up
26JOIN
27    `user` u
28ON    `up`.`user_id` = `u`.`user_id`
29
30LEFT JOIN (
31    SELECT
32        `up`.*
33    FROM
34        `user_table` up
35    JOIN (
36        SELECT
37            `user_id`, MAX(`unixtimes`) unixtimes 
38        FROM 
39            `user_table` 
40        WHERE
41            `status` = 'a'
42        GROUP BY
43            `user_id`) all_approved
44    ON 
45        `up`.`user_id` = `all_approved`.`user_id` AND `up`.`unixtimes` = `all_approved`.`unixtimes`) record_current
46ON 
47    `up`.`user_id` = `record_current`.`user_id`
48
49WHERE 
50    `up`.`status` = 'p'
51ORDER BY 
52    `up`.`unixtimes`;