CODEWITHSUNDEEP
mysqlsqlselect
aured91
aured91

Reputation: 23

Select information from multiple tables with unique ID in the table name

Here is the scenario: I have tables stored in my monitoring application database using IDs as part of the table name. For instance, for 10 monitored devices, the device logs are stored in tables for each device with the device ID as part of the name, like this:

Device ID      Table Name
1              device_logs.log_1
2              device_logs.log_2

I want to be able to :

select * from all device log tables where ID IN (a-list-of-IDs)

I reference this type of information a lot, and it would be easier to do it in a quick query and possibly a report. For some small list of devices, a union query works, but after about 4-5 devices, that gets too long. Programmatically, I can do this in python with string substitution, but how do you do it in MySQL as a query?

Adding a code segment I am trying to get to work, but struggling with the syntax:

    drop table if exists tmp_logs;
create temporary table tmp_logs
(
device_name varchar(30) not null,
date datetime,
message varchar (255)
)
engine=innodb;
drop procedure if exists load_tmp_log_data;

delimiter #
create procedure load_tmp_log_data()
begin

declare vid int unsigned;

  truncate table tmp_logs;
  start transaction;
  while vid in(4976,4956) do
    insert into tmp_logs values 

(           SELECT 
                dev.device,
                date,
                message

                FROM device_logs.logs_vid

                inner join master_dev.legend_device dev on dev.id=vid

                where date >= '2014-7-1'

                and message not like '%HDMI%'
                and message not like '%DVI%'
                and message not like '%SIP%'
                and message not like '%Completed%'
                and message not like '%Template%'
                and message not like '%collection%'
                and message not like '%Cache%'
                and message not like '%Disconnect%'
                and message not like '%Row removed%'
                and message not like '%detailed discovery%'
                and message not like '%restarted%'
                and message not like '%Auto Answer%'

);

  end while;
  commit;
end #

delimiter ;

call load_tmp_log_data();

select * from tmp_logs order by device_name;

Upvotes: 1

Views: 797

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You cannot dynamically specify the user name as a query in regular SQL. You could use a prepare statement with dynamic SQL.

An alternative is to set up the query as:

select l.*
from ((select l.*
       from device_logs.log_1 l
       where device_id = 1 and id in (list of ids)
      ) union all
      (select l.*
       from device_logs.log_2 l
       where device_id = 2 and id in (list of ids)
      ) . . . 
     ) l;

You need to repeat the conditions in each subquery -- that makes them more efficient. And, use union all instead of union to avoid duplication.

Fundamentally, though, having tables of the same structure is often a sign of poor database design. It would be much better to have a single table with a column specifying the id. Then your query would be really easy:

select l.*
from logs l
where id in (list of ids);

You could generate such a table by changing the application that creates the tables. Or you could generate such a table by using an insert trigger on each of the subtables. Or, if the data can be a day or so out of date, run a job that re-creates the table every night.

Upvotes: 1

Related Questions