How do I join multiple result sets from a WHILE loop in a mySQL query?

Question

This is the problem that I currently have.

I have 7 tables of trip data. What I am currently doing is selecting at random, a Card_ID from the third table, then searching through all 7 tables with that Card_ID and selecting all record made by that particular Card_ID.

The problem is that many result sets are generated, and this presents problems since I will have to manually export them one by one. Is there anyway to 'automate' this process by combining about like 2000 WHILE loops of records into one result set?

Thanks in advance.

BEGIN
DECLARE counter INT;
DECLARE random INT;
DECLARE cardid VARCHAR(20);
SET counter = 1;

WHILE counter < 801 DO

    SET random = FLOOR(1 + (RAND() * 5451696));
    SET cardid = (SELECT CARD_ID FROM trips13042011 WHERE TripID = random);

    SELECT * FROM trips11042011 WHERE CARD_ID=cardid 
    UNION ALL
    SELECT * FROM trips12042011 WHERE CARD_ID=cardid
    UNION ALL
    SELECT * FROM trips13042011 WHERE CARD_ID=cardid
    UNION ALL
    SELECT * FROM trips14042011 WHERE CARD_ID=cardid    
    UNION ALL
    SELECT * FROM trips15042011 WHERE CARD_ID=cardid
    UNION ALL
    SELECT * FROM trips16042011 WHERE CARD_ID=cardid
    UNION ALL
    SELECT * FROM trips17042011 WHERE CARD_ID=cardid

    ORDER BY Ride_Start_Date, Ride_Start_Time ASC;

    SET counter = counter + 1;

end WHILE;
END

A temporary table, perhaps? Although, "best" would likely be to eliminate the loop .. but I'm not sure how that'd be done in MySQL with the non-deterministic random function. (Perhaps generate the random numbers - all 801 of them - *first* and then JOIN?) — , Jan 31 '13 at 05:18
If I understood correctly you have currently 2000 "day tables" ( tripsxxyyzzzz in dd-mm-zzzz -format ) and you'd like to create a STORED PROCEDURE that searches for all the day tables automatically and makes a union query with them. — Antti Rytsölä, Jan 31 '13 at 05:45
Found this one for help: http://stackoverflow.com/questions/4823550/create-a-stored-procedure-to-iterate-through-a-list-of-tables-and-truncate-them — Antti Rytsölä, Jan 31 '13 at 05:46
@anttir That answer looks like it uses SQL Server [*Table Variables*](http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/) (they are "sort of" like local Temporary Tables) - I am not sure if MySQL has such a construct. — , Jan 31 '13 at 05:47
Another, for mysql: http://stackoverflow.com/a/4756624/468921 Modify the line on "DECLARE c1 " to limit the query only to trip tables and to modify the query. — Antti Rytsölä, Jan 31 '13 at 05:53
hi @anttir, I currently have around 800 tables/ result sets for each individual, represented by their Card ID. These individual result sets contain records from all 7 days. What I would like to do is to combine all these 800 tables into 1 table for easy export. Hope this clarification helps. — EJ Chua, Jan 31 '13 at 05:54

score 2 · Answer 1 · answered Jan 31 '13 at 05:28

I am not familiar with the language you are using (mySQL scripting code??), but to my comprehension you might try something like this :

-Do your while loop to create an array of 801 (or whatever you need) randoms elements. -Add the array into the where clause -> WHERE CARD_ID IN (308, 3746, 72827, 1, etc...) until the 801st element of your array

DECLARE rdmArray ARRAY;

WHILE counter < 801 DO
     SET random = FLOOR(1 + (RAND() * 5451696));
     SET cardid = (SELECT CARD_ID FROM trips13042011 WHERE TripID = random);
     rdmArray.push_back(cardid );
     SET counter = counter + 1;
end WHILE;

SELECT * FROM trips11042011 WHERE CARD_ID IN (//element of rdmArray here) 
UNION ALL
SELECT * FROM trips12042011 WHERE CARD_ID IN (//element of rdmArray here)
UNION ALL
SELECT * FROM trips13042011 WHERE CARD_ID IN (//element of rdmArray here)
UNION ALL
SELECT * FROM trips14042011 WHERE CARD_ID IN (//element of rdmArray here)   
UNION ALL
SELECT * FROM trips15042011 WHERE CARD_ID IN (//element of rdmArray here)
UNION ALL
SELECT * FROM trips16042011 WHERE CARD_ID IN (//element of rdmArray here)
UNION ALL
SELECT * FROM trips17042011 WHERE CARD_ID IN (//element of rdmArray here)

ORDER BY Ride_Start_Date, Ride_Start_Time ASC;

...

To my understanding of your question, that is what I guess need to be done.

Hope this help!

antoine

hi antoine, as I am a newbie to mySQL and am just using this for my final year thesis, I am writing this procedure entirely in mySQL Workbench. So yeah, I believe I am using mySQL scripting code. — EJ Chua, Jan 31 '13 at 05:40

How do I join multiple result sets from a WHILE loop in a mySQL query?

1 Answers1