Query : Select field from multiple tables at once

[ktsangop]

Hello everybody.
I'd like some help with a query.

I have a database named 'base1' which contains many tables.
Some of the tables have names like 'track1' , 'track2' etc... which have identical columns.

I would like to select all the tables beginning with track and show the data of a specific column named 'ammount'.

Thanks in advance.

[debasisdas]

You need to join the tables to fetch the data from multiple tables.

[kishan112]

Maybe you can use this:

(SELECT ammount FROM track1) UNION (SELECT ammount FROM track2) UNION (SELECT ammount FROM track3)

[ktsangop]

Thanks for the answers.
It seems like i have to use cursors which i am not familiar with but it is time for me to learn..

The following code

(SELECT ammount FROM track1) UNION (SELECT ammount FROM track2) UNION (SELECT ammount FROM track3)

requires that you know which tables you have to select. I want it to be dynamic cause the number of tables beginning with 'track' changes all the time.

I found that this returns the tables i want to use :

SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_name LIKE 'track%'

Now i am trying to find a way to execute a query seperately in all of the tables the first line returns and join the results.

[ktsangop]

Can someone find my error on the following code?
On execution i get the error :
Table 'base1.tablename' doesn't exist

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc5`()
BEGIN
  DECLARE done BOOL DEFAULT FALSE;
  DECLARE tablename VARCHAR (20);
  DECLARE tracktables CURSOR FOR
  SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_name LIKE 'track%';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
    SET done = TRUE;

  OPEN tracktables;

  myloop: LOOP
    FETCH tracktables INTO tablename;
    IF done THEN
      CLOSE tracktables;
      LEAVE myloop;
    END IF;

    SELECT ammount from tablename;
  END LOOP;
END

I suppose that my usage of tablename cursor isn't correct but how should i use it?

Thanks in advance..