Is it possible to build such a query in sql?

Question

MySQL

Hey!
We have such a problem. There is a table in a DB, in the table there are records. Everything is as usual, 1 entry - 1 ID. When you create a new record, it is added to the database, it has a special parent field. And when adding a new record, the field is equal to 0. When this record is changed, a new one is actually created, but the parent field is now not equal to zero, but the id of the record that was created for the first time. The bottom line here is that all versions of a particular article are displayed in the admin panel. What article was at the very beginning, and how did it change later. Hence so many records. But now there is a need to display these records on the site, of course, their latest versions. Here's how to build such a query so that, roughly speaking, all posts with parent = 0 (all new posts) are displayed, provided that these articles have no children (changed posts with parent = id of this article).
Whether it is possible to make it generally through request?

Answer 1

id | parent_id
1 | 0
2 | 0
3 | 1 (here it is - the first edition of the first entry)
4 | 2 (first edition of the second entry)
5 | 0
6 | 1 (second edition of the first entry)
7 | 2 (second edition of the second entry)

First, determine the algorithm for yourself
1. Select all records with parent_id = 0 (this is all your articles)
2. Select records with parent_id = id (from the selection of records in paragraph 1) and the maximum id of the record (get all the latest revisions of articles)
3. Select records with parent_id = 0 and id is not in the parent_id field from the record selection of item 2 (articles that do not have editions)
4. Combine the record sets of item 2 and item 3
You only need to describe this algorithm in SQL

Answer 2

Can.
Select all rows with all "daughters" using OUTER JOIN, use grouping by id and count, and then WHERE [count] = 1.
upd: Sorry, not WHERE, but HAVING, of course.

Answer 3

Alas, I do not write on the muscle, because the example is for MS SQL,
but I suppose with minimal edits it will work on the muscle.

drop table if exists t1;
create table t1
(Id int primary key, Parent_Id int, code nvarchar(10), TextValue nvarchar(max));
-- Если у стаьи с разной версионностью есть набор полей (или одно поле, у меня это CODE)
-- Которые гарантированно уникальны, тогда все много проще

insert into t1(Id, Parent_Id, code, TextValue) values (1,0, 'Message', 'The variant');
insert into t1(Id, Parent_Id, code, TextValue) values (2,1, 'Message', 'The variant.');
insert into t1(Id, Parent_Id, code, TextValue) values (3,2, 'Message', 'Variant.');
insert into t1(Id, Parent_Id, code, TextValue) values (4,0, 'Letter', 'Hi dear Kat');
insert into t1(Id, Parent_Id, code, TextValue) values (5,4, 'Letter', 'Hi Kat');

-- запрос обычный вложенный
select * from t1 a 
join (select code, max(Parent_Id) Parent_Id from t1 group by code )b 
on a.code = b.code and a.Parent_Id = b.Parent_Id;
-- но если появляется такой "колобок" как описано ниже
-- у нас колобок с Id 6 и колобок с Id 8 это последнии 
-- ревизии разных статей с одним кодом.
insert into t1(Id, Parent_Id, code, TextValue) values (6,0, 'Kolobok', 'K1');
insert into t1(Id, Parent_Id, code, TextValue) values (7,0, 'Kolobok', 'K2');
insert into t1(Id, Parent_Id, code, TextValue) values (8,7, 'Kolobok', 'K3');
-- тогда запрос выйдаст не красивый результат
select * from t1 a 
join (select code, max(Parent_Id) Parent_Id from t1 group by code )b 
on a.code = b.code and a.Parent_Id = b.Parent_Id;

-- нужно делать иерархический запрос, например такой.
with z (Kod, id, parent_id, code, TextValue) as 
(
  select Kod=a.id, a.id, a.parent_id, a.code, a.TextValue from t1 a where parent_id = 0
  union all 
  select z.Kod, a.id, a.parent_id, a.code, a.TextValue from t1 a
  join z on a.Parent_Id = z.id
)
select z.* from z join (select Kod, id = max(id) from z group by Kod ) x on z.Id = x.id;