SQL - Looping through ever row of table in MySQL?

Question

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!!

" I know there's a set based approach, but I don't want to do it that way." Why on earth not? That what an RDBMS is good at? — Karl Kieninger, Jun 11 '15 at 18:10
Well I already have a set-based version actually... but I'm required not to do this in a set based format unfortunately. — ocean800, Jun 11 '15 at 18:10
@ocean800 Do you need only one row (the "closest") from movement? — Barranka, Jun 11 '15 at 18:12
The reason performance is terrible on cursors is because they do just this. If you want to "loop through the rows", use a cursor - that's what a cursor does (and is also why it performs poorly). Is there a good reason you aren't trying to do this in a set based manner? — Dan Field, Jun 11 '15 at 18:13
@Barranka I need every `movement` row that has the closest `timestamp` to a row of the `communication`. — ocean800, Jun 11 '15 at 18:14
@DanField I see.. so the only way to do it is through `cursors`? Unfortunately, yes, I can't use a set-based approach. — ocean800, Jun 11 '15 at 18:16
You can implement your own cursor (which you're trying to do here), but you might as well use the working implementation of it unless you have a very specific optimization you think you can pull off. — Dan Field, Jun 11 '15 at 18:17
@DanField I see.. thanks for the help. I'll look more into cursors then. — ocean800, Jun 11 '15 at 18:19
@ocean800 I think I wasn't clear enough... do you need only one movement for each communication record? If you need all movements for each communication, why don't you just write a query with an inner join and appropriate ordering criteria? — Barranka, Jun 11 '15 at 18:24
@ocean800, I'd still recommend a set based approach - cursors should generally only be used as a last resort. I'm having trouble thinking of when you'd really ever **need** to use them. — Dan Field, Jun 11 '15 at 18:25
@Barranka Yes, I only need one movement for each communication record. Anyway, the problem is that I'm required to write a procedure for this, in fact I already have a working set-based approach. — ocean800, Jun 11 '15 at 18:29
Not to kick a dead horse, but avoiding set-based logic without reason is just that: unreasonable. If that is being forced upon you, then you need to fight it tooth and nail: explain it in small words and show them a million links (easily found) explaining why loops in RDBMS are stupid. Don't give in. — johnjps111, Jun 11 '15 at 18:38

Barranka · Accepted Answer · 2015-06-11T20:11:31.157

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:

Thank you so much!! I can't even tell you how much this helps :) — ocean800, Jun 11 '15 at 19:02
@ocean800 happy to help. I've edited my answer to include the links to the relevant reference manual sections. I suggest you read them. Cheers — Barranka, Jun 11 '15 at 20:25

SQL - Looping through ever row of table in MySQL?

1 Answers1

Linked