I have this problem joining across multiple tables and grouping by columns on those tables. I spent a few hours at work today with no success, I think I have a misunderstanding of joins or multiple column grouping.
I have included an SQL dump at the end of this post so you can see the database for yourself – it’ll probably explain better than my post is about to..!
Basically, it’s a WordPress blog which needs the three ‘latest posts’ pulled out of the database. The complication is only one post from each category – so you can’t pull out three latest posts and have them all in the same category.
I thought this would be a simple ‘group by’ on the post id, and the category id (term_id), followed by a LIMIT 3?
In this example though I’ll try and pull out 5 latest articles from unique categories.
So there are four tables:
- wp_posts, which contains the blog posts. I only want to return
post_type of ‘post’ from this table. - wp_terms, this defines ‘key words’.
- wp_term_taxonomy, a table which defines what each keyword is – e.g.,
is it a category. - wp_term_relationships, which links the ‘key words’ in wp_terms to posts.
You will see the tables and demo data below. Quickly though, what I thought – clearly incorrectly – would be a query like this:
SELECT * FROM wp_posts
INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.id
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
INNER JOIN wp_terms ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_posts.post_type = 'post'
GROUP BY wp_term_taxonomy.term_id wp_posts.id,
ORDER BY post_date DESC
LIMIT 5
With the following data in the tables, I would expect the above query to return Training Puppy, Bathroom Makeover, Post Nachos and Sewing Buttons. The last two would get ignored as they are duplicate post ids or term ids. But instead, I get those four followed by Changing Fuses.
What is the correct way to query for the data that I want?
table wp_posts
+----+-------------------+---------------------+-----------+
| id | post_title | post_date | post_type |
+----+-------------------+---------------------+-----------+
| 5 | Boiling Eggs | 2009-06-11 22:57:07 | post |
| 6 | Boiling Eggs | 2009-06-11 22:57:05 | revision |
| 2 | About | 2009-06-11 22:55:37 | page |
| 7 | Changing Fuses | 2009-06-11 22:57:19 | post |
| 8 | Changing Fuses | 2009-06-11 22:57:15 | revision |
| 9 | Sewing Buttons | 2009-06-11 22:57:28 | post |
| 10 | Sewing Buttons | 2009-06-11 22:57:25 | revision |
| 11 | Posh Nachos | 2009-06-11 22:57:35 | post |
| 12 | Posh Nachos | 2009-06-11 22:57:34 | revision |
| 13 | Bathroom Makeover | 2009-06-11 22:57:44 | post |
| 14 | Bathroom Makeover | 2009-06-11 22:57:41 | revision |
| 15 | Training Puppy | 2009-06-11 22:57:51 | post |
| 16 | Training Puppy | 2009-06-11 22:57:48 | revision |
| 17 | Boiling Eggs | 2009-06-11 22:57:07 | revision |
| 18 | Training Puppy | 2009-06-11 22:57:51 | revision |
| 19 | Bathroom Makeover | 2009-06-11 22:57:44 | revision |
| 20 | Posh Nachos | 2009-06-11 22:57:35 | revision |
+----+-------------------+---------------------+-----------+
table wp_terms
+---------+---------------+---------------+------------+
| term_id | name | slug | term_group |
+---------+---------------+---------------+------------+
| 1 | Uncategorized | uncategorized | 0 |
| 2 | Blogroll | blogroll | 0 |
| 3 | Food | foot | 0 |
| 4 | DIY | diy | 0 |
| 5 | Crafts | crafts | 0 |
| 6 | Pets | pets | 0 |
| 7 | puppy | puppy | 0 |
| 8 | dog | dog | 0 |
| 9 | training | training | 0 |
| 10 | bathroom | bathroom | 0 |
| 11 | rennovate | rennovate | 0 |
| 12 | mexican | mexican | 0 |
| 13 | snack | snack | 0 |
| 14 | fast food | fast-food | 0 |
+---------+---------------+---------------+------------+
table wp_term_taxonomy
+------------------+---------+---------------+-------------+--------+-------+
| term_taxonomy_id | term_id | taxonomy | description | parent | count |
+------------------+---------+---------------+-------------+--------+-------+
| 1 | 1 | category | | 0 | 0 |
| 2 | 2 | link_category | | 0 | 7 |
| 3 | 3 | category | | 0 | 2 |
| 4 | 4 | category | | 0 | 2 |
| 5 | 5 | category | | 0 | 1 |
| 6 | 6 | category | | 0 | 1 |
| 8 | 7 | post_tag | | 0 | 1 |
| 9 | 8 | post_tag | | 0 | 1 |
| 10 | 9 | post_tag | | 0 | 1 |
| 11 | 10 | post_tag | | 0 | 1 |
| 12 | 11 | post_tag | | 0 | 1 |
| 13 | 12 | post_tag | | 0 | 1 |
| 14 | 13 | post_tag | | 0 | 1 |
| 15 | 14 | post_tag | | 0 | 1 |
+------------------+---------+---------------+-------------+--------+-------+
table wp_term_relationships
+-----------+------------------+------------+
| object_id | term_taxonomy_id | term_order |
+-----------+------------------+------------+
| 1 | 2 | 0 |
| 2 | 2 | 0 |
| 3 | 2 | 0 |
| 4 | 2 | 0 |
| 5 | 2 | 0 |
| 6 | 2 | 0 |
| 7 | 2 | 0 |
| 6 | 1 | 0 |
| 18 | 1 | 0 |
| 8 | 1 | 0 |
| 7 | 4 | 0 |
| 10 | 1 | 0 |
| 9 | 5 | 0 |
| 12 | 1 | 0 |
| 11 | 3 | 0 |
| 14 | 1 | 0 |
| 13 | 4 | 0 |
| 16 | 1 | 0 |
| 15 | 6 | 0 |
| 17 | 1 | 0 |
| 5 | 3 | 0 |
| 15 | 8 | 0 |
| 15 | 9 | 0 |
| 15 | 10 | 0 |
| 19 | 1 | 0 |
| 13 | 11 | 0 |
| 13 | 12 | 0 |
| 20 | 1 | 0 |
| 11 | 13 | 0 |
| 11 | 14 | 0 |
| 11 | 15 | 0 |
+-----------+------------------+------------+
SQL Dump
CREATE TABLE IF NOT EXISTS `wp_posts` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
`post_author` bigint(20) unsigned NOT NULL default '0',
`post_date` datetime NOT NULL default '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
`post_content` longtext NOT NULL,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`post_status` varchar(20) NOT NULL default 'publish',
`comment_status` varchar(20) NOT NULL default 'open',
`ping_status` varchar(20) NOT NULL default 'open',
`post_password` varchar(20) NOT NULL default '',
`post_name` varchar(200) NOT NULL default '',
`to_ping` text NOT NULL,
`pinged` text NOT NULL,
`post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
`post_content_filtered` text NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL default '0',
`guid` varchar(255) NOT NULL default '',
`menu_order` int(11) NOT NULL default '0',
`post_type` varchar(20) NOT NULL default 'post',
`post_mime_type` varchar(100) NOT NULL default '',
`comment_count` bigint(20) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
--
-- Dumping data for table `wp_posts`
--
INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(5, 1, '2009-06-11 22:57:07', '2009-06-11 21:57:07', 'fadgagadf', 'Boiling Eggs', '', 'publish', 'open', 'open', '', 'boiling-eggs', '', '', '2009-06-11 22:58:03', '2009-06-11 21:58:03', '', 0, 'http://project1.searbe.co.uk/?p=5', 0, 'post', '', 0),
(6, 1, '2009-06-11 22:57:05', '2009-06-11 21:57:05', '', 'Boiling Eggs', '', 'inherit', 'open', 'open', '', '5-revision', '', '', '2009-06-11 22:57:05', '2009-06-11 21:57:05', '', 5, 'http://project1.searbe.co.uk/?p=6', 0, 'revision', '', 0),
(2, 1, '2009-06-11 22:55:37', '2009-06-11 21:55:37', 'This is an example of a WordPress page, you could edit this to put information about yourself or your site so readers know where you are coming from. You can create as many pages like this one or sub-pages as you like and manage all of your content inside of WordPress.', 'About', '', 'publish', 'open', 'open', '', 'about', '', '', '2009-06-11 22:55:37', '2009-06-11 21:55:37', '', 0, 'http://project1.searbe.co.uk/?page_id=2', 0, 'page', '', 0),
(7, 1, '2009-06-11 22:57:19', '2009-06-11 21:57:19', 'fgsgfafga', 'Changing Fuses', '', 'publish', 'open', 'open', '', 'changing-fuses', '', '', '2009-06-11 22:57:19', '2009-06-11 21:57:19', '', 0, 'http://project1.searbe.co.uk/?p=7', 0, 'post', '', 0),
(8, 1, '2009-06-11 22:57:15', '2009-06-11 21:57:15', '', 'Changing Fuses', '', 'inherit', 'open', 'open', '', '7-revision', '', '', '2009-06-11 22:57:15', '2009-06-11 21:57:15', '', 7, 'http://project1.searbe.co.uk/?p=8', 0, 'revision', '', 0),
(9, 1, '2009-06-11 22:57:28', '2009-06-11 21:57:28', 'ghsghgs', 'Sewing Buttons', '', 'publish', 'open', 'open', '', 'sewing-buttons', '', '', '2009-06-11 22:57:28', '2009-06-11 21:57:28', '', 0, 'http://project1.searbe.co.uk/?p=9', 0, 'post', '', 0),
(10, 1, '2009-06-11 22:57:25', '2009-06-11 21:57:25', '', 'Sewing Buttons', '', 'inherit', 'open', 'open', '', '9-revision', '', '', '2009-06-11 22:57:25', '2009-06-11 21:57:25', '', 9, 'http://project1.searbe.co.uk/?p=10', 0, 'revision', '', 0),
(11, 1, '2009-06-11 22:57:35', '2009-06-11 21:57:35', 'hhjhdh', 'Posh Nachos', '', 'publish', 'open', 'open', '', 'posh-nachos', '', '', '2009-06-11 22:59:34', '2009-06-11 21:59:34', '', 0, 'http://project1.searbe.co.uk/?p=11', 0, 'post', '', 0),
(12, 1, '2009-06-11 22:57:34', '2009-06-11 21:57:34', '', 'Posh Nachos', '', 'inherit', 'open', 'open', '', '11-revision', '', '', '2009-06-11 22:57:34', '2009-06-11 21:57:34', '', 11, 'http://project1.searbe.co.uk/?p=12', 0, 'revision', '', 0),
(13, 1, '2009-06-11 22:57:44', '2009-06-11 21:57:44', 'hjhjdhjdjdh', 'Bathroom Makeover', '', 'publish', 'open', 'open', '', 'bathroom-makeover', '', '', '2009-06-11 22:59:19', '2009-06-11 21:59:19', '', 0, 'http://project1.searbe.co.uk/?p=13', 0, 'post', '', 0),
(14, 1, '2009-06-11 22:57:41', '2009-06-11 21:57:41', '', 'Bathroom Makeover', '', 'inherit', 'open', 'open', '', '13-revision', '', '', '2009-06-11 22:57:41', '2009-06-11 21:57:41', '', 13, 'http://project1.searbe.co.uk/?p=14', 0, 'revision', '', 0),
(15, 1, '2009-06-11 22:57:51', '2009-06-11 21:57:51', 'hjhjdjdy', 'Training Puppy', '', 'publish', 'open', 'open', '', 'training-puppy', '', '', '2009-06-11 22:59:05', '2009-06-11 21:59:05', '', 0, 'http://project1.searbe.co.uk/?p=15', 0, 'post', '', 0),
(16, 1, '2009-06-11 22:57:48', '2009-06-11 21:57:48', '', 'Training Puppy', '', 'inherit', 'open', 'open', '', '15-revision', '', '', '2009-06-11 22:57:48', '2009-06-11 21:57:48', '', 15, 'http://project1.searbe.co.uk/?p=16', 0, 'revision', '', 0),
(17, 1, '2009-06-11 22:57:07', '2009-06-11 21:57:07', 'fadgagadf', 'Boiling Eggs', '', 'inherit', 'open', 'open', '', '5-revision-2', '', '', '2009-06-11 22:57:07', '2009-06-11 21:57:07', '', 5, 'http://project1.searbe.co.uk/?p=17', 0, 'revision', '', 0),
(18, 1, '2009-06-11 22:57:51', '2009-06-11 21:57:51', 'hjhjdjdy', 'Training Puppy', '', 'inherit', 'open', 'open', '', '15-revision-2', '', '', '2009-06-11 22:57:51', '2009-06-11 21:57:51', '', 15, 'http://project1.searbe.co.uk/?p=18', 0, 'revision', '', 0),
(19, 1, '2009-06-11 22:57:44', '2009-06-11 21:57:44', 'hjhjdhjdjdh', 'Bathroom Makeover', '', 'inherit', 'open', 'open', '', '13-revision-2', '', '', '2009-06-11 22:57:44', '2009-06-11 21:57:44', '', 13, 'http://project1.searbe.co.uk/?p=19', 0, 'revision', '', 0),
(20, 1, '2009-06-11 22:57:35', '2009-06-11 21:57:35', 'hhjhdh', 'Posh Nachos', '', 'inherit', 'open', 'open', '', '11-revision-2', '', '', '2009-06-11 22:57:35', '2009-06-11 21:57:35', '', 11, 'http://project1.searbe.co.uk/?p=20', 0, 'revision', '', 0);
-- --------------------------------------------------------
--
-- Table structure for table `wp_terms`
--
CREATE TABLE IF NOT EXISTS `wp_terms` (
`term_id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(200) NOT NULL default '',
`slug` varchar(200) NOT NULL default '',
`term_group` bigint(10) NOT NULL default '0',
PRIMARY KEY (`term_id`),
UNIQUE KEY `slug` (`slug`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
--
-- Dumping data for table `wp_terms`
--
INSERT INTO `wp_terms` (`term_id`, `name`, `slug`, `term_group`) VALUES
(1, 'Uncategorized', 'uncategorized', 0),
(2, 'Blogroll', 'blogroll', 0),
(3, 'Food', 'foot', 0),
(4, 'DIY', 'diy', 0),
(5, 'Crafts', 'crafts', 0),
(6, 'Pets', 'pets', 0),
(7, 'puppy', 'puppy', 0),
(8, 'dog', 'dog', 0),
(9, 'training', 'training', 0),
(10, 'bathroom', 'bathroom', 0),
(11, 'rennovate', 'rennovate', 0),
(12, 'mexican', 'mexican', 0),
(13, 'snack', 'snack', 0),
(14, 'fast food', 'fast-food', 0);
-- --------------------------------------------------------
--
-- Table structure for table `wp_term_relationships`
--
CREATE TABLE IF NOT EXISTS `wp_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL default '0',
`term_taxonomy_id` bigint(20) unsigned NOT NULL default '0',
`term_order` int(11) NOT NULL default '0',
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `wp_term_relationships`
--
INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES
(1, 2, 0),
(2, 2, 0),
(3, 2, 0),
(4, 2, 0),
(5, 2, 0),
(6, 2, 0),
(7, 2, 0),
(6, 1, 0),
(18, 1, 0),
(8, 1, 0),
(7, 4, 0),
(10, 1, 0),
(9, 5, 0),
(12, 1, 0),
(11, 3, 0),
(14, 1, 0),
(13, 4, 0),
(16, 1, 0),
(15, 6, 0),
(17, 1, 0),
(5, 3, 0),
(15, 8, 0),
(15, 9, 0),
(15, 10, 0),
(19, 1, 0),
(13, 11, 0),
(13, 12, 0),
(20, 1, 0),
(11, 13, 0),
(11, 14, 0),
(11, 15, 0);
-- --------------------------------------------------------
--
-- Table structure for table `wp_term_taxonomy`
--
CREATE TABLE IF NOT EXISTS `wp_term_taxonomy` (
`term_taxonomy_id` bigint(20) unsigned NOT NULL auto_increment,
`term_id` bigint(20) unsigned NOT NULL default '0',
`taxonomy` varchar(32) NOT NULL default '',
`description` longtext NOT NULL,
`parent` bigint(20) unsigned NOT NULL default '0',
`count` bigint(20) NOT NULL default '0',
PRIMARY KEY (`term_taxonomy_id`),
UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
KEY `taxonomy` (`taxonomy`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;
--
-- Dumping data for table `wp_term_taxonomy`
--
INSERT INTO `wp_term_taxonomy` (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES
(1, 1, 'category', '', 0, 0),
(2, 2, 'link_category', '', 0, 7),
(3, 3, 'category', '', 0, 2),
(4, 4, 'category', '', 0, 2),
(5, 5, 'category', '', 0, 1),
(6, 6, 'category', '', 0, 1),
(8, 7, 'post_tag', '', 0, 1),
(9, 8, 'post_tag', '', 0, 1),
(10, 9, 'post_tag', '', 0, 1),
(11, 10, 'post_tag', '', 0, 1),
(12, 11, 'post_tag', '', 0, 1),
(13, 12, 'post_tag', '', 0, 1),
(14, 13, 'post_tag', '', 0, 1),
(15, 14, 'post_tag', '', 0, 1);
update: I apologize for flubbing my understanding of your original question. I skimmed it and thought it was another example of the “top N from each group” question I see so frequently.
Here’s a solution to your original question, which is the most recent five posts, with at most one post from each category:
Here’s the output in my test:
PS: Many thanks for posting the DDL and INSERT statements in your original question! Not many folks asking SQL questions do that.
Below is my first answer, based on my incorrect understanding of the question:
So you want all posts such that there are fewer than five other posts with the same category term and a more recent
post_date?You need to join to the
poststable twice:This is much simplier version but thanks to your idea bill :