For a messaging system on a site I need to display all messages to a specific user (1028) that has not been hidden.
In each record set I am only interested in the last record of the message set to a specific user, that is not hidden.
Here is a digram that gives an example dataset and results that I would expect, and hopefully explains it better
http://www.davidrouse.co.uk/diagram.jpg
Here is a create table & dataset for my query…
CREATE TABLE IF NOT EXISTS messages2
(
messageID
int(11) NOT NULL AUTO_INCREMENT,
site_userID
int(4) DEFAULT NULL,
root_key
int(4) NOT NULL DEFAULT ‘0’,
hidden
tinyint(1) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (messageID
)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;
INSERT INTO messages2
(messageID
, site_userID
, root_key
, hidden
) VALUES
(1, 1028, 1, 0),
(2, 12, 1, 0),
(3, 1028, 1, 1),
(4, 12, 1, 0),
(5, 1028, 2, 0),
(6, 12, 2, 0),
(7, 1028, 2, 0),
(8, 12, 2, 0);