Set result of one column to the value of another in LEFT JOIN statement

Question

I am joining two rows from the same table which are related, basically a "root row" and the latest updated row. I am hoping there is a way to set the value of a root column row to a row in the latest update's. Only in the result that is returned, I don't want to update the database row of the latest column.

/* Table Name:  posts */
+--------+---------+-------------+--------------+
|   ID   |   ROOT  |   PUBLISHED |   DELETED    |
+--------+---------+-------------+--------------+
|   01   |     01  |    FALSE    |     NULL     |
+--------+---------+-------------+--------------+
|   02   |     01  |     NULL    |  01/03/2019  |
+--------+---------+-------------+--------------+
|   03   |     01  |     NULL    |  01/04/2019  |
+--------+---------+-------------+--------------+
|   04   |     01  |     NULL    |     NULL     |
+--------+---------+-------------+--------------+

The query I'm trying to do selects row 04 because it's the latest, and I'm trying to join the root row, (01) and then make the value of row 04 have "FALSE" in the return result, while remaining "NULL" in the database.

SELECT p1.*, p2.ID, p2.PUBLISHED
FROM posts p1
LEFT JOIN posts p2 ON p1.ID = p2.ID
WHERE p1.DELETED = NULL

I've tried a few things and have been looking around but I have just been getting stuck.

SELECT p1.*, p2.ID, p2.PUBLISHED, p1.PUBLISHED = p2.PUBLISHED
FROM posts p1
LEFT JOIN posts p2 ON p1.ID = p2.ID
WHERE p1.DELETED = NULL

SELECT p1.*, p2.ID, p2.PUBLISHED, p1.PUBLISHED as p2.PUBLISHED
FROM posts p1
LEFT JOIN posts p2 ON p1.ID = p2.ID
WHERE p1.DELETED = NULL

Thank you anyone who reads this

You can't compare `column = NULL`, you must use `column IS NULL`. Possible duplicate of https://stackoverflow.com/questions/3536670/mysql-selecting-rows-where-a-column-is-null/3536676#3536676 — Bill Karwin, Jan 10 '19 at 19:39

score 1 · Answer 1 · answered Jan 10 '19 at 21:03

You can join a subquery getting the greatest, undeleted ID. Then also do a self join to get the root.

SELECT p1.id,
       p1.root,
       p4.published,
       p1.deleted
       FROM posts p1
            INNER JOIN (SELECT max(p2.id) id
                               FROM posts p2
                               WHERE p2.deleted IS NULL) p3
                       ON p3.id = p1.id
            INNER JOIN posts p4
                       ON p4.id = p1.root;

Set result of one column to the value of another in LEFT JOIN statement

1 Answers1