CODEWITHSUNDEEP
mysqlsql
carpics
carpics

Reputation: 2282

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

I am new with mysql and I need some help.

I have 10 tables in my database.

On of them is named as "hibernate" and schema is like this

enter image description here

Column "entity" contains names of other tables in database.

What I need is query which will return me one row for each table, with 3 column:

  1. table name
  2. max(id) of the table
  3. the value of the next_hi column in hibernate table for the table

Like this:

enter image description here

It would be great if it could do for each "hibernate" table and pick up TableName from "entity" column and get MaxId for that TableName and return row like picture above with "next_hi" also

EDIT:

If it's not possible to get this by reading Table names from "hibernate" table it will also help if I can get this working with Table names hardoced in Query.

I have example which 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 don't know how to modify this query to return this for multiple tables.

Upvotes: 4

Views: 1362

Answers (1)

Blag
Blag

Reputation: 5894

Best way for me is with 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 ;

On every table you put this simple trigger that update a last_id field on your hibernate table.

With this a simple select * from hibernate will give you what you want...

But this is not really optimized. It work. After it depend when you need this data. maybe an hard coded version is better...

If you want to go with the 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

Upvotes: 2

Related Questions