Get max id for each entry from another related table-CodePudding

I have 2 tables namely entry and entry_log whose schema is as below:

entry:

id NUMBER PRIMARY KEY

name VARCHAR2(100)

entry_log:

id NUMBER PRIMARY KEY

parent_id NUMBER NOT NULL --> constraint el FOREIGN KEY (parent_id) REFERENCES entry ( id )

user_id NUMBER NOT NULL --> constaint rl_uk FOREIGN KEY (user_id) REFERENCES user ( id )

Note: User is another table that I have.

For every row in entry table I can have multiple rows in the entry_log table, where entry log table actually holds parent_id and the user who made the modification to a row in entry table.

Basically, entry is the actual table and a row is inserted in the entry_log table every time a create or update occurs in the entry table.

I need to have a query such that it returns the following columns: id from entry table name from entry table max(id) from entry_log table where parent_id in entry_log = id in entry table

I have the below query which works but I want to achieve this without having to use subquery to improve performance.

            select max(log_id) as log_id from (
                SELECT
                    entry_log.id log_id,
                    entry_log.parent_id
                FROM
                    entry
                INNER JOIN entry_log ON entry_log.parent_id = entry.id
            ) group by parent_id
        )
        SELECT
            entry.id,
            entry.name,
            mif.log_id "MAX_ID",
        FROM
            entry
        INNER JOIN entry_log ON entry_log.parent_id = entry.id
        INNER JOIN max_id_finder mif ON mif.log_id = entry_log.id
        WHERE 1=1

Is there any better way to achieve this without impacting performance?

CodePudding user response：

Pl check if the following solution works for you:

select a.*, b.* from entry a
inner join (select id, max(p_id) as p_id, `name` from entry_log group by p_id) b
on a.id = b.p_id

Dataset used:

CREATE TABLE `entry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

CREATE TABLE `entry_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_p_id` (`p_id`),
  CONSTRAINT `fk_p_id` FOREIGN KEY (`p_id`) REFERENCES `entry` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

insert into entry values (1, "A1");
insert into entry values (2, "A2");
insert into entry values (3, "A3");
insert into entry values (4, "A4");
insert into entry values (5, "A5");
insert into entry values (6, "A6");
insert into entry values (7, "A7");
insert into entry values (8, "A8");


insert into entry_log values (1, 1, "P001");
insert into entry_log values (2, 2, "P002");
insert into entry_log values (3, 1, "P003");
insert into entry_log values (4, 2, "P004");
insert into entry_log values (5, 3, "P005");
insert into entry_log values (6, 1, "P006");
insert into entry_log values (7, 2, "P007");
insert into entry_log values (8, 5, "P008");
insert into entry_log values (9, 2, "P007");
insert into entry_log values (10, 4, "P008");
insert into entry_log values (11, 7, "P001");
insert into entry_log values (12, 8, "P002");
insert into entry_log values (13, 6, "P003");
insert into entry_log values (14, 6, "P004");
insert into entry_log values (15, 3, "P005");
insert into entry_log values (16, 7, "P006");
insert into entry_log values (17, 2, "P007");
insert into entry_log values (18, 5, "P008");

CodePudding user response：

Thanks user1300830

The below query works perfectly.

SELECT
    entry.id,
    entry.name,
    entry_log.log_id,
FROM
    entry
INNER JOIN (SELECT max(id) as log_id, parent_id from entry_log group by parent_id) entry_log ON entry_log.parent_id = entry.id
WHERE 1=1