CODEWITHSUNDEEP
mysqlsqlstored-procedurescursorunique
Nɪsʜᴀɴᴛʜ ॐ
Nɪsʜᴀɴᴛʜ ॐ

Reputation: 2914

Selecting unique values from the result set using MySQL stored procedures

Selecting distinct values from the above result set i.e, thereby eliminating duplicate values and finally storing these values into a variable with a comma-separated list of values. Such that variable assigned with a comma-separated list of values must be given as an input to another SQL IN Operator

DELIMITER $$

USE `someDB`$$

DROP PROCEDURE IF EXISTS `AAA`$$

CREATE PROCEDURE `AAA`(IN `feed_setting_user_id` BIGINT)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  DECLARE FoFID, FoFUsername, friendID, friendUsername TEXT;
  DECLARE exit_loop BOOLEAN DEFAULT FALSE;
    DECLARE friend_cursor CURSOR FOR 

        SELECT `u`.`ID`, `u`.`username` FROM `users` `u`  
        WHERE `u`.`ID` IN (SELECT `u1`.`ID` FROM users `u1` 
            WHERE `u1`.`ID` IN 
            (SELECT `uf`.`friendid` FROM user_friends `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = feed_setting_user_id ) 
             OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = feed_setting_user_id) 
             AND `u1`.`ID` != feed_setting_user_id);

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

        OPEN friend_cursor;
        friend_loop: LOOP
            FETCH FROM friend_cursor INTO friendID, friendUsername;
            IF exit_loop THEN
                LEAVE friend_loop;
            END IF;
            -- SELECT  friendID, friendUsername;
            SELECT `u`.`ID`, `u`.`username` FROM `users` `u` WHERE `u`.`ID` NOT IN (feed_setting_user_id) AND `u`.`ID` 
                            IN (SELECT `u1`.`ID` FROM `users` `u1` 
                            WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM `user_friends` `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = friendID AND `uf`.`friendid` != friendID) 
                            OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = friendID AND `uf2`.`userid` != friendID) AND `u1`.`ID` != friendID ); 
        END LOOP friend_loop;
        CLOSE friend_cursor;
END $$

DELIMITER ;

Actual Result:

+----+-----------+
| ID | username  |
+----+-----------+
|  5 | SpiderMan |
|  8 | AntMan    |
|  9 | Bat       |
| 11 | SuperMan  |
| 12 | Arrow     |
| 13 | CAmerica  |
+----+-----------+
6 rows in set (0.53 sec)

+----+----------+
| ID | username |
+----+----------+
|  9 | Bat      |
| 10 | BatMan   |
| 13 | mustafa  |
+----+----------+
3 rows in set (0.61 sec)

+----+-----------+
| ID | username  |
+----+-----------+
|  5 | SpiderMan |
|  6 | Hulk      |
|  9 | Bat       |
| 10 | BatMan    |
+----+-----------+
4 rows in set (0.69 sec)

+----+----------+
| ID | username |
+----+----------+
|  8 | AntMan   |
|  9 | Bat      |
| 10 | BatMan   |
| 11 | SuperMan |
+----+----------+
4 rows in set (0.78 sec)

Query OK, 0 rows affected (0.86 sec)

Expected Result:

5,6,8,9,11,12,10,13

We need to assign the above list of comma separated values into a variable. So that we need to eliminate duplicates from the above list of values.

Update:

Tried to implement nested cursors 

DELIMITER $$

USE `someDB`$$

DROP PROCEDURE IF EXISTS `AAA`$$

CREATE PROCEDURE `AAA`(IN `feed_setting_user_id` BIGINT)
    READS SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
  DECLARE FoFID, FoFUsername, friendID, friendUsername TEXT;
  DECLARE exit_loop BOOLEAN DEFAULT FALSE;
  DECLARE exit_loop1 BOOLEAN DEFAULT FALSE;
    DECLARE friend_cursor CURSOR FOR 

        SELECT `u`.`ID`, `u`.`username` FROM `users` `u`  
        WHERE `u`.`ID` IN (SELECT `u1`.`ID` FROM users `u1` 
            WHERE `u1`.`ID` IN 
            (SELECT `uf`.`friendid` FROM user_friends `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = feed_setting_user_id ) 
             OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = feed_setting_user_id) 
             AND `u1`.`ID` != feed_setting_user_id);

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

        OPEN friend_cursor;
        friend_loop: LOOP
            FETCH FROM friend_cursor INTO friendID, friendUsername;
            IF exit_loop THEN
                LEAVE friend_loop;
            END IF;
--          SELECT  friendID, friendUsername;


            DECLARE friend_of_friend_cursor CURSOR FOR 

             SELECT `u`.`ID`, `u`.`username` FROM `users` `u` WHERE `u`.`ID` NOT IN (feed_setting_user_id) AND `u`.`ID` 
                            IN (SELECT `u1`.`ID` FROM `users` `u1` 
                            WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM `user_friends` `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = friendID AND `uf`.`friendid` != friendID) 
                            OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = friendID AND `uf2`.`userid` != friendID) AND `u1`.`ID` != friendID );
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop1 = TRUE;               

            OPEN friend_of_friend_cursor;
                friend_of_friend_loop: LOOP
                    FETCH FROM friend_of_friend_cursor INTO FoFID, FoFUsername;
                    IF exit_loop1 THEN
                        LEAVE friend_of_friend_loop;
                    END IF;

                SELECT  FoFID, FoFUsername;

                END LOOP friend_of_friend_loop;
            CLOSE friend_of_friend_cursor;

        END LOOP friend_loop;
        CLOSE friend_cursor;
END $$

DELIMITER ;

Results:

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE friend_of_friend_cursor CURSOR FOR 

             SELECT `u`.`ID`, `u`.`user' at line 29

Upvotes: 1

Views: 744

Answers (1)

James
James

Reputation: 1829

Try the below sp

DELIMITER $$

USE `someDB`$$

DROP PROCEDURE IF EXISTS `AAA`$$

CREATE PROCEDURE `AAA`(IN `feed_setting_user_id` BIGINT)
    modifies SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
DECLARE v_friendID, FoFID bigint; # use datatype which is used for u.ID used int based on ur result
DECLARE exit_loop, exit_loop1 BOOLEAN DEFAULT FALSE;

DECLARE friend_cursor CURSOR FOR 
    SELECT `u`.`ID` FROM `users` `u`  
    WHERE `u`.`ID` IN (SELECT `u1`.`ID` FROM users `u1` 
    WHERE `u1`.`ID` IN 
    (SELECT `uf`.`friendid` FROM user_friends `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = feed_setting_user_id ) 
    OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = feed_setting_user_id) 
    AND `u1`.`ID` != feed_setting_user_id);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

drop temporary table if exists unique_tbl; #precaution: when sp stops with error 
create temporary table unique_tbl (user_id bigint unique); #to avoid duplicate added unique

insert into unique_tbl values (feed_setting_user_id); # added input from sp

    OPEN friend_cursor;
    friend_loop: LOOP

        FETCH friend_cursor INTO v_friendID;

        IF exit_loop THEN
            LEAVE friend_loop;
        ELSE

            replace into unique_tbl values (v_friendID); # since we need all unique id's using replace if exists

            fof: begin

                DECLARE friend_of_friend_cursor CURSOR FOR 
                    SELECT `u`.`ID` FROM `users` `u` WHERE `u`.`ID` NOT IN (feed_setting_user_id) AND `u`.`ID` 
                    IN (SELECT `u1`.`ID` FROM `users` `u1` 
                    WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM `user_friends` `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = v_friendID AND `uf`.`friendid` != v_friendID) 
                    OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = v_friendID AND `uf2`.`userid` != v_friendID) 
                    AND `u1`.`ID` != v_friendID );

                DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop1 = TRUE;

                OPEN friend_of_friend_cursor;
                friend_of_friend_loop: LOOP

                    FETCH friend_of_friend_cursor INTO FoFID;

                    IF exit_loop1 THEN
                        LEAVE friend_of_friend_loop;
                    END IF;

                    replace into unique_tbl values (FoFID);

                END LOOP friend_of_friend_loop;
                CLOSE friend_of_friend_cursor;
            end;
        end if;
    END LOOP friend_loop;
    CLOSE friend_cursor;

select group_concat(user_id) from unique_tbl; #this will show result in comma seperated 2,34,56,78

#cleanup
drop temporary table if exists unique_tbl;

END $$

DELIMITER ;

Upvotes: 1

Related Questions