I've this issue joining across multiple tables and grouping by posts on individuals tables. I spent a couple of hrs at the office today without results, I believe I've got a misunderstanding of joins or multiple column grouping.

I've incorporated an SQL dump in the finish of the publish so that you can begin to see the database on your own - it'll most likely explain much better than my publish is going to..!

Essentially, it is a WordPress blog which needs the 3 'latest posts' drawn from the database. The complication is just one publish from each category - which means you can't take out three latest posts and also have all of them within the same category.

I figured this is an easy 'group by' around the publish id, and also the category id (term_id), then a restriction 3?

Within this example though I'll try to take out 5 latest articles from unique groups.

So you will find four tables:

  • wordpress_posts, which consists of your blog posts. I simply want to return publish_kind of 'post' out of this table.
  • wordpress_terms, this defines 'key words'.
  • wordpress_term_taxonomy, a table which defines what each keyword is - e.g., could it be a category.
  • wordpress_term_associations, which links the 'key words' in wordpress_terms to posts.

You will notice the tables and demo data below. Rapidly though, things i thought - clearly improperly - will be a query such as 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

Using the following data within the tables, I'd expect the above mentioned query to come back Training Puppy, Bathroom Transformation, Publish Nachos and Sewing Buttons. The final two would get overlooked because they are duplicate publish ids or term ids. But rather, I recieve individuals four then Altering Fuses.

What's the right way to question for that data which i want?

table wordpress_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 wordpress_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 wordpress_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 wordpress_term_associations

+-----------+------------------+------------+
| 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);