MySQL LOOP For Collecting Data from Multiple Tables

I've looked around and can't seem to quite find a solution that meets my needs, so I'm hoping you can help. (I'm somewhat new to MySQL, though I do have a good deal of SQL Server and Oracle experience to draw from.)

I have a database with several tables.

The first table is a definition table that stores basic information and is called cp_def:

cid (pkey)  status
-------------------
10001       0
10002       1

Then, for each record in the cp_def table, there is a corresponding cp_[cid] table (where [cid] is the cid value from the cp_def table). Example:

table: cp_10001

id(pkey)   code      date_issued    date_expired
-------------------------------------------------
1          ABC123    2011-06-23     2011-06-30
2          CYG124    2011-06-23     2011-06-30


table: cp_10002

id(pkey)   code      date_issued    date_expired
-------------------------------------------------
1          CAC126    2011-06-23     2011-06-30
2          VGC254    2011-06-23     2011-06-30

I need to run a dynamic query every day (as a part of a shell script and cron job) to output the code and date_expired column values for ALL records in all cp_[cid] tables, where the date_issued value is the previous day's date.

I have a query working (see below). However, every time I add a new cp_[CID] table, it requires me to manually append another UNION ALL statement to end of the query.

SELECT code, date_expired
FROM mydatabase.cp_10001
WHERE DATE(date_issued) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
UNION ALL
SELECT code, date_expired
FROM mydatabase.cp_10002
WHERE DATE(date_issued) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);  

My ultimate goal is to programmaticly generate the above query, at the time it's executed, so that every time a new cp_[CID] table is added to the database, it doesn't require manual intervention.

I tried using a view. However, it still seemed like adding a table was going to require a manual ALTERING of the view, to account for the new table.

Given that each cp_[CID] table is listed in the cp_def table (as well as the INFORMATION_SCHEMA table), I was thinking that I could perform some sort of loop to build a dynamic query, but I haven't quite figured it out. In the interest of keeping this short, I'll avoid posting my failed attempts at looping through the cp_def table to create a dynamic query.

Any assistance you could provide in pointing me in the right direction for crafting such a query would be greatly appreciated. 开发者_JS百科If I've overlooked something, I do apologize. I've tried search for exactly what I'm looking for with many different phrases, to no avail.

I'd prefer to have one table with a structure like this (instead of many cp_####) -

cp_properties:
id(pkey), cid(foreign key to cp_def.cid) , code      date_issued    date_expired

But for you design, try this query -

SELECT GROUP_CONCAT(CONCAT('SELECT code, date_expired FROM mydatabase.cp_', cid, ' WHERE DATE(date_issued) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)') SEPARATOR '\r\nUNION ALL\r\n') FROM mydatabase.cp_def

This query generates a select statements with UNION ALL clauses, then execute generated query with prepared statements.

EDIT

SELECT GROUP_CONCAT(CONCAT('SELECT code, date_expired FROM mydatabase.cp_', cid, ' WHERE DATE(date_issued) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)') SEPARATOR '\r\nUNION ALL\r\n') INTO @s FROM mydatabase.cp_def;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

First off, why is the cp_ data split into seperate tables? If the data was properly normalized this would not be an issue.

Givn that, a simple solution would be to create a view of a union of all the cp_ tables - however IME, mysql does not optimise push-predicates for views/sub-queries as efficiently as it might. And as you have discovered you still need to maintain the view.

Which leads me to using the solution described by Devart. Noet that this is not a substitute for normalizing your data! It will never be as efficient and as the number of tables increases, performance will degrade rapidly. However Devart has skipped over the fiddly bit - how to get the resultset out of the procedure - the short answer is that you can't. However you can insert the matched rows into a temporary table then select the rows from that.

I've found the solution:

Save the UNION ALL query on tables to @s and create a view to save the result of combination of the tables.

Select group_concat(concat('select * from new_schema.', name ) separator '\r\nUNION 
ALL\r\n')
into @s from new_schema.tempTableName;
set @v = concat('create View view as ', @s);
PREPARE stmt FROM @v;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;