SQL - Looping through ever row of table in MySQL?

ocean800 picture ocean800 · Jun 11, 2015 · Viewed 8.4k times · Source

So I have 2 tables, communication,and movement.

communication has columns fromID,timestamp that has ID of caller, and time the call was made. Then I have another table movement that has ID,timestamp,x,y, that has the ID of a person, their location (x,y), and the time that they are at that location.

I want to write a query that looks something like this:

For every single row of communication(R)
    SELECT * FROM movement m
    WHERE m.ID = R.fromID && m.timestamp <= R.timestamp
    ORDER BY timestamp 

Basically, what this is doing is finding the closest movement timestamp for a given communication timestamp. After that, eventually, I want to find the location (x,y) of a call, based on the movement data.

How would I do this? I know there's a set based approach, but I don't want to do it that way. I looked into cursors, but I get the feeling that the performance is terrible on that.

So is there anyway to do this with a loop? I essentially want to loop through every single row of the communication, and get the result.

I tried something like this:

DELMITER $$ 
CREATE PROCEDURE findClosestTimestamp() 
BEGIN 
DECLARE commRowCount DEFAULT 0; 
DECLARE i DEFAULT 0; 
DECLARE ctimestamp DEFAULT 0; 
SELECT COUNT(*) FROM communication INTO commRowCount; 

SET i = 0; 
WHILE i < commRowCount DO 
SELECT timestamp INTO ctimestamp FROM communication c 
SELECT * FROM movement m 
WHERE m.vID = c.fromID && m.timestamp <= R.timestamp
END$$ 
DELIMITER ; 

But I know that's completely wrong.

Is the only way to do this cursors? I just can't find an example of this anywhere on the internet, and I'm completely new to procedures in SQL.

Any guidance would be greatly appreciated, thank you!!

Answer

Barranka picture Barranka · Jun 11, 2015

Let's see if I can point you in the right direction using cursors:

delimiter $$
create procedure findClosestTimeStamp()
begin
    -- Variables to hold values from the communications table
    declare cFromId int;
    declare cTimeStamp datetime;
    -- Variables related to cursor:
    --    1. 'done' will be used to check if all the rows in the cursor 
    --       have been read
    --    2. 'curComm' will be the cursor: it will fetch each row
    --    3. The 'continue' handler will update the 'done' variable
    declare done int default false;
    declare curComm cursor for
        select fromId, timestamp from communication; -- This is the query used by the cursor.
    declare continue handler for not found -- This handler will be executed if no row is found in the cursor (for example, if all rows have been read).
        set done = true;

    -- Open the cursor: This will put the cursor on the first row of its
    -- rowset.
    open curComm;
    -- Begin the loop (that 'loop_comm' is a label for the loop)
    loop_comm: loop
        -- When you fetch a row from the cursor, the data from the current
        -- row is read into the variables, and the cursor advances to the
        -- next row. If there's no next row, the 'continue handler for not found'
        -- will set the 'done' variable to 'TRUE'
        fetch curComm into cFromId, cTimeStamp;
        -- Exit the loop if you're done
        if done then
            leave loop_comm;
        end if;
        -- Execute your desired query.
        -- As an example, I'm putting a SELECT statement, but it may be
        -- anything.
        select *
        from movement as m
        where m.vID = cFromId and m.timeStamp <= cTimeStamp
        order by timestampdiff(SECOND, cTimeStamp, m.timeStamp)
        limit 1;
    end loop;
    -- Don't forget to close the cursor when you finish
    close curComm;
end $$
delimiter ;

References: