Mysql - Need to get latest of table A when referenced from table B

I'm building a bug tracker type tool for kicks. I'm having probs with a small prob relating to version control of my data.

I have a table 'action' where I store all the data about the action (desription, who entered it, status etc). I also have a action_status table where each time the status is changed (from not asigned, in progress, complete etc) it is logged here.. What I can't seem to do is list the actions with their latest status value. You'll note that the status table has two rows, one has been submitted, the otehr has not.. I ONLY want to see the row that has submitted = 0 (the latest date I'd presume..)

to make matters worse, each action has a revision Id and if the action text is changed, I'm creating a new entry in the action table with the same ID, but a new revision ID.. this however is working great.. but I thought I should mention in case it's interfering with my problem.

Here are my tables and some sample data: Am I being a monkey?

CREATE TABLE IF NOT EXISTS `action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_action` int(11) NOT NULL,
  `id_priority` int(11) NOT NULL,
  `revision` int(11) NOT NULL DEFAULT '1',
  `reference` varchar(255) NOT NULL,
  `department` int(11) NOT NULL,
  `id_parent` int(11) NOT NULL DEFAULT '0',
  `sort_order` int(11) NOT NULL,
  `description` text NOT NULL,
  `date_start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_end` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `action`
--

INSERT INTO `action` (`id`, `id_action`, `id_priority`, `revision`, `reference`, `department`, `id_parent`, `sort_order`, `description`, `date_start`, `date_end`, `date_created`) VALUES
(1, 1, 1, 1, '1', 1, 0, 2, 'Test Action revision test 1 a', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(2, 1, 1, 2, '0', 1, 0, 2, 'Test Action revision test 1 b', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(3, 2, 1, 1, '0', 1, 0, 1, 'Test Action revision test 2 a', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(4, 2, 1, 2, '0', 1, 0, 1, 'Test Action revision test 2 b', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(5, 3, 2, 1, '0', 1, 0, 0, 'Test Action revision test 3 b', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `action_status`
--

CREATE TABLE IF NOT EXISTS `action_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_department` int(11) NOT NULL,
  `id_priority` int(11) NOT NULL,
  `id_action` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `submitted` tinyint(4) NOT NULL,
  `approved` tinyint(4) NOT NULL,
  `published` tinyint(4) NOT NULL,
  `date_now` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `action_status`
--

INSERT INTO `action_status` (`id`, `id_department`, `id_priority`, `id_action`, `status`, `submitted`, `approved`, `published`, `date_now`) VALUES
(1, 1, 1, 2, 3, 1, 1, 1, '2011-06-20 16:36:09'),
(2, 1, 1, 2, 5, 0, 0, 0, '2011-06-20 16:40:09');


CREATE TABLE IF NOT EXISTS `priority` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `priority`
--

INSERT INTO `priority` (`id`, `description`) VALUES
(1, 'Test Priority'),
(2, '2nd Priority');

And my 'problem' SQL

SELECT `action`.`id_priority`, `priority`.`description` as priority, `action`.`reference`, `action`.`description` as action, `action`.`id_action`, `action`.`date_start`, `action`.`date_end`, `action`.`id_parent`, `action_status`.`status`, `action`.`revision`, `action_status`.`submitted`, `action_status`.`date_now`

FROM (`action`)

LEFT JOIN action_status ON
  `action_status`.`id_action` = `action`.`id_action`

JOIN `priority` ON
  `action`.`id_priority` = `priority`.`id`

WHERE
action.department = 1 AND
action.revision =(SELECT MAX(ar.revision) FROM action as ar WHERE action.id_action 开发者_如何学编程= ar.id_action)

GROUP BY `action`.`id_action`

ORDER BY `id_priority` asc, `id_parent` asc, `sort_order` asc

---

one good recommendation - if you ask for help, simplify your example as much as possible to show the basic root of your problem. No one will try to understand your complex code this way... What I can't seem to do is list the actions with their latest status value. You speak about latest status value, so I'd expect that you want the latest record from action_status table, but in your query you check for max revision in action table... And why you group by action_id, when you want to compaI must admit I don't understand the design at all.

Anyway, I think I was solving similar problem few weeks ago, see this MySQL feature reguest ( http://bugs.mysql.com/bug.php?id=2020 ) and my reply at '7 Jul 13:12'. See the following example, it should help you:

Suppose you have table with price for each goods in each store. For each goods, you want to see the minimal price and the related store, in which you get it for the price! Exactly the same as in your example - you want a record with max revision.

create table prices ( goods varchar(10), price double, store varchar(10) );

insert into prices values ('car', 200, 'Amazon'), ('car', 150, 'CarStore'), ('Bread', 2, 'Baker1'), ('Bread', 1, 'Baker2');

select goods, min(price), (select store from prices as p where p.goods = prices.goods
order by price limit 1) as store
from prices
group by goods;

Hope this helps. If you want to pick more columns, just pick an id instead of store and make it as a subquery and join the table again via the id.

---

If I understood it well and you need only actions with max revision, try something like this:

SELECT *

FROM

    (SELECT `action`.`id_action`, max(`action`.`revision`), 
        (select id 
         from action as a 
         where a.id_action = action.id_action 
         order by revision desc 
         limit 1) as id_with_max_revision

    FROM `action`

    WHERE
    action.department = 1

    GROUP BY `action`.`id_action`

    ) as action_max_revision
JOIN action on action_max_revision.id_with_max_revision = action.id
LEFT JOIN action_status ON
      `action_status`.`id_action` = `action`.`id_action`

JOIN `priority` ON
      `action`.`id_priority` = `priority`.`id`
ORDER BY `id_priority` asc, `id_parent` asc, `sort_order` asc

The inner query selects actions with max revision, and the outer query does the other gimcrackery which is not the core of the problem :-)