title: How do I join multiple result sets from a WHILE loop in a mySQL query?
body: <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>