We help IT Professionals succeed at work.

Join to expand to a certain number of rows in MySql

Daniel Wilson
on
Medium Priority
268 Views
Last Modified: 2012-05-12
I have a MySql table which, simplified, looks like:

ID - int - auto_identity
iDate - Date
Ext - varchar(25)
Calls - int
AvgTalkMinutes - Decimal

Sample data would look like
1  2011-11-01  200   24    3.1532

It needs to expand to go into a table that has 1 record per call.  Basically, I'm de-summarizing it -- I know, weird situation ... importing from a system that provides less data.

I want the output of some join to have, in this case 24 rows with
2011-11-01   200   3.1532


So, what can I join to in MySql to give me X rows?  I believe dual serves that purpose in Oracle ... and elsewhere I've created a ListOfNumbers table just so I could join Where Number<=Calls.

Is an artificial ListOfNumbers table the best option in MySql?  Or is there a better way?

Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Commented:
The short answer is that listofnumbers is probably the easiest way to do this if the number of calls is predictable.

So, if

SELECT MAX(Calls) FROM table

will reliably be less than 1000, for example, then you would make a listofnumbers table and join to it.

A more robust solution is to make a stored procedure and declare a cursor to the first table and make a loop to insert the new rows.


CERTIFIED EXPERT

Commented:
here's a rough example

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `normalize`()
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE _ext VARCHAR(25);
DECLARE _calls INT;
DECLARE _idate DATE;
DECLARE _avg DECIMAL;
DECLARE counter INT;
DECLARE cur1 CURSOR FOR SELECT ext,calls,idate,avgtalkminutes FROM t1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DELETE FROM t2;
OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO _ext, _calls, _idate, _avg;
   
    IF done THEN
      LEAVE read_loop;
    END IF;

SET counter = 0;

write_loop: LOOP


INSERT t2 (ext, idate, avgtalkminutes) VALUES (_ext, _idate, _avg);
SET counter = counter + 1;
IF counter = _calls THEN LEAVE write_loop; end IF;
END LOOP write_loop;
   
  END LOOP read_loop;

END

Author

Commented:
Thanks for the cursor example.  I haven't used those in MySQL yet.

Still ... I think your first comment ... that ListOfNumbers is more appropriate here ... is accurate.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.