How do I join multiple result sets from a WHILE loop in a mySQL query? <p>This is the problem that I currently have. </p> <p>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.</p> <p>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?</p> <p>Thanks in advance.</p> <pre><code>BEGIN DECLARE counter INT; DECLARE random INT; DECLARE cardid VARCHAR(20); SET counter = 1; WHILE counter &lt; 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 </code></pre>