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?) — user166390
– user166390, Commented Jan 31, 2013 at 5: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ä
– Antti Rytsölä, Commented Jan 31, 2013 at 5:45
Found this one for help: stackoverflow.com/questions/4823550/… — Antti Rytsölä
– Antti Rytsölä, Commented Jan 31, 2013 at 5:46
@anttir That answer looks like it uses SQL Server Table Variables (they are "sort of" like local Temporary Tables) - I am not sure if MySQL has such a construct. — user166390
– user166390, Commented Jan 31, 2013 at 5:47
Another, for mysql: 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ä
– Antti Rytsölä, Commented Jan 31, 2013 at 5:53

antoine · Accepted Answer · 2013-01-31 05:28:30Z

2

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

answered Jan 31, 2013 at 5:28

antoine

2442 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EJ Chua Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related