MySQL getting DISTINCT ID when joining a table with itself

Question

I was trying to teach myself something new and having a look at the way WordPress structures it's tables to work with revisions.

The structure is (sorta) as follows:

+----+------------+---------------------+------------+-----------------+-------------+
| ID | post_title |      post_date      | post_name  |   post_content  | post_parent |
+----+------------+---------------------+------------+-----------------+-------------+
|  1 | Foo        | 2012-09-20 10:00:00 | Foo        | Bar             |           0 |
|  2 | Arrrrr     | 2012-09-20 10:05:00 | Arrrr      | Pirates!        |           0 |
|  3 | Arrrrrr    | 2012-09-20 10:06:00 | revision-1 | Argh pirates    |           2 |
|  4 | Arrrrrr    | 2012-09-20 10:06:00 | revision-2 | Argh piratessss |           2 |
+----+------------+---------------------+------------+-----------------+-------------+

Now, I'd like to make a query that gives me ONLY the parent rows (Foo and Arrrr) with their ID's, BUT with their revised content.

I came up with following query:

SELECT original.ID, revision.post_title, revision.post_content, revision.post_name FROM wp_posts AS original
INNER JOIN wp_posts AS revision ON original.ID = revision.post_parent
WHERE original.post_status = 'publish'
AND original.post_parent = 0
ORDER BY original.ID, revision.ID DESC

This gives me following result:

+----+------------+---------------------+------------+-----------------+-------------+
| ID | post_title |      post_date      | post_name  |   post_content  | post_parent |
+----+------------+---------------------+------------+-----------------+-------------+
|  1 | Foo        | 2012-09-20 10:00:00 | Foo        | Bar             |           0 |
|  2 | Arrrrrr    | 2012-09-20 10:06:00 | revision-2 | Argh piratessss |           2 |
|  2 | Arrrrrr    | 2012-09-20 10:06:00 | revision-1 | Argh pirates    |           2 |
|  2 | Arrrrr     | 2012-09-20 10:05:00 | Arrrr      | Pirates!        |           0 |
+----+------------+---------------------+------------+-----------------+-------------+

But I'd like to further reduce it to:

+----+------------+---------------------+------------+-----------------+-------------+
| ID | post_title |      post_date      | post_name  |   post_content  | post_parent |
+----+------------+---------------------+------------+-----------------+-------------+
|  1 | Foo        | 2012-09-20 10:00:00 | Foo        | Bar             |           0 |
|  2 | Arrrrrr    | 2012-09-20 10:06:00 | revision-2 | Argh piratessss |           2 |
+----+------------+---------------------+------------+-----------------+-------------+

I've tried adding DISTINCT to the SELECT, and adding GROUP BY original.ID but both didn't give me the desired result.

Assuming ID is autoincrement you should be ordering your posts by `ORDER BY original.ID, inherit.ID` instead of `ORDER BY original.post_date, inherit.post_date DESC` And, to me, renaming the second table (alias) to `revision` makes more sense — Germann Arlington, Sep 20 '12 at 08:22
I've changed the query as you said, you're right that revision makes more sense. I see no difference in using the post_date or the ID, they're both as fast... — Bram, Sep 20 '12 at 08:50
Using IDs is not about speed, but about correct sequence: you have 2 revisions with the same date, but they will have different IDs. Are you trying to get one latest revision for each post? Then group by original.ID to get MAX(inherit.ID) — Germann Arlington, Sep 20 '12 at 09:01

score 0 · Accepted Answer · answered Sep 20 '12 at 09:45

While you can't distinguish different revisions of one topic for each other except date you can do the following:

select * from wp_posts p join
                (
                    select max(post_date) as max_dtm, id
                    from wp_posts
                    group by if(post_parent = 0, id, post_parent)
                ) v on p.id = v.id and p.post_date = v.max_dtm;

MySQL getting DISTINCT ID when joining a table with itself

1 Answers1