headzoo
asked on
MySQL: Most Recent Item From Category
I want to list all the post categories, while also showing the most recent post from that category. Can't figure this one out. Here are example tables:
CREATE TABLE `categories` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`body` text NOT NULL,
`date_created` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `posts_to_categories` (
`post_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB;
And some sample data:
INSERT INTO `categories` (`id`, `name`) VALUES
(1, 'Vacation'),
(2, 'Family');
INSERT INTO `posts` (`id`, `title`, `body`, `date_created`) VALUES
(1, 'Paris', 'Here are some pictures', '2009-04-11 15:45:39'),
(2, 'Spain', 'What a great trip!', '2009-04-12 15:45:52'),
(3, 'The kids', 'The latest pictures', '2009-04-11 15:46:26'),
(4, 'Mom & Dad', 'Enjoying mom''s birthday', '2009-04-12 15:46:44');
INSERT INTO `posts_to_categories` (`post_id`, `category_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2);
The query would produce results like this:
1) Vacation
- What a great trip!
2) Family
- Enjoying mom's birthday
That is, each category, along with the category's most recent post.
CREATE TABLE `categories` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
`body` text NOT NULL,
`date_created` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `posts_to_categories` (
`post_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB;
And some sample data:
INSERT INTO `categories` (`id`, `name`) VALUES
(1, 'Vacation'),
(2, 'Family');
INSERT INTO `posts` (`id`, `title`, `body`, `date_created`) VALUES
(1, 'Paris', 'Here are some pictures', '2009-04-11 15:45:39'),
(2, 'Spain', 'What a great trip!', '2009-04-12 15:45:52'),
(3, 'The kids', 'The latest pictures', '2009-04-11 15:46:26'),
(4, 'Mom & Dad', 'Enjoying mom''s birthday', '2009-04-12 15:46:44');
INSERT INTO `posts_to_categories` (`post_id`, `category_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2);
The query would produce results like this:
1) Vacation
- What a great trip!
2) Family
- Enjoying mom's birthday
That is, each category, along with the category's most recent post.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.