CODEWITHSUNDEEP
phpmysqlpdo
Brownman Revival
Brownman Revival

Reputation: 3850

UNION mysql determine which table the result came from

$stmt = $dbh->prepare("SELECT id FROM table1 UNION SELECT id FROM table2 UNION SELECT id FROM table3 ORDER BY id DESC LIMIT 1");

The code above is my code for auto generating ID. I want to select the last inserted ID. What I want to do is I want to get the last ID inserted on three tables. It can be from any of the three tables. And then i want to increment that ID. But the catch is i need to know which table the last ID is from so i can select another field and record that the last ID has a certain attribute. This attribute is depending on the table that is why i want to get the table.

Upvotes: 0

Views: 230

Answers (2)

spencer7593
spencer7593

Reputation: 108410

Add a discriminator column, and use the MAX aggregate function for each query to avoid sorting a huge intermediate resultset, and use UNION ALL set operator in place of UNION operator. (Since each query will return only one row, that's not going to make much of a difference; but where we don't need to eliminate duplicate rows, we prefer the UNION ALL set operator to avoid the unnecessary (and sometimes expensive) operation.

Something like this should return the result you seem to be after:

( SELECT 'table1'   AS source
       , MAX(t1.id) AS max_id
    FROM table1 t1
)
UNION ALL
( SELECT 'table2'   AS source
       , MAX(t2.id) AS max_id
    FROM table2 t2
)
UNION ALL
( SELECT 'table3' AS source
     , MAX(t3.id) AS max_id
  FROM table3 t3
)
ORDER BY max_id DESC
LIMIT 1

That will give you a resultset that identifies the table name that had the maximum id.

NOTE: This assumes that the "last inserted id" is identified by the maximum value. If two tables have the same maximum id value, it's indeterminate which row will be returned. You can add source to the ORDER BY to make it deterministic:

ORDER BY max_id DESC, source DESC

(The actual requirements aren't clear to me; the statement above should return the same value that was being returned by the query in the question, along with a discriminator which tells which table the id value is from.)

Reference: https://dev.mysql.com/doc/refman/5.5/en/union.html

NOTE: This may satisfy your use case, but in the more general case, we advise avoiding this approach to get the id value of the row that was last inserted.

For values of an auto_increment column, where the value is automatically assigned, the last_insert_id function will return the id value of the first row that was inserted by the immediately preceding INSERT statement in the same session. In a multiuser system, it is not safe to assume that the "highest" id value was a row inserted by the current session - that approach is effectively broken.

Upvotes: 1

Vipin PS
Vipin PS

Reputation: 432

Try to get last inserted ID of all three tables and with inserted time then differentiate based on inserted time

Upvotes: 0

Related Questions