Mysql returns Max (id) for multiple tables along with one value from one specific table

Question

I am new to mysql and I need help.

I have 10 tables in my database.

On of them is called "sleep mode", and the circuit is similar to this.

The entity column contains the names of other tables in the database.

I need a query that will return me one row for each table, with 3 columns:

• table name
• max (id) tables
• next_hi column value in hibernate table for table

Like this:

It would be great if it could be done for each hibernate table and get the TableName from the entity column and get the MaxId for that TableName and return a row similar to the picture above using next_hi also

EDIT:

If this cannot be obtained by reading the table names from the "hibernate" table, this will also help if I can get this to work with table names locked in Query.

I have an example that I made for one table "Account":

SELECT hibernate.entity as TableName, hibernate.next_hi, MAX(Account.Id) as MaxId From Account INNER JOIN hibernate ON "Account"=hibernate.entity; 

But now I do not know how to modify this query to return it for multiple tables.

Answer 1

The best way for me is with a trigger:

  -- change end of line DELIMITER DELIMITER // DROP TRIGGER IF EXISTS `trg_account_last_id`; // CREATE TRIGGER `trg_account_last_id` AFTER INSERT ON `Account` FOR EACH ROW BEGIN UPDATE `hibernate` SET `hibernate`.`last_id` = NEW.`id` WHERE `hibernate`.`entity` = 'Account'; END; // DELIMITER ; 

In each table, you place this simple trigger that updates the last_id field in the hibernate table.

With this simple select * from hibernate you get what you want ...

---

But this is not very optimized. It works. After that, when you need this data. perhaps a hard coded version is better ...

If you want to go with hard-coded:

 ( SELECT `hibernate`.`entity` as `TableName`, `hibernate`.`next_hi`, (SELECT MAX(`Id`) FROM `Account`) as `MaxId` FROM `hibernate` WHERE `hibernate`.`entity` = "Account" ) UNION ALL ( SELECT `hibernate`.`entity` as `TableName`, `hibernate`.`next_hi`, (SELECT MAX(`Id`) FROM `Page`) as `MaxId` FROM `hibernate` WHERE `hibernate`.`entity` = "Page" ) UNION ALL ( SELECT `hibernate`.`entity` as `TableName`, `hibernate`.`next_hi`, (SELECT MAX(`Id`) FROM `User`) as `MaxId` FROM `hibernate` WHERE `hibernate`.`entity` = "User" ) -- and again for other tables