CODEWITHSUNDEEP
mysql
prathap
prathap

Reputation: 39

Mysql functions output as part of where condition in clause

Created a new function in mysql using GROUP_CONCAT. My Function as follows:

    CREATE  FUNCTION `Get_customerCodes`(customer_id int(11)) 
    RETURNS varchar(500) CHARSET utf8
    BEGIN
    Declare Result VARCHAR(1000);
        set Result =  (select GROUP_CONCAT(concat('\'', customer_code,'\'') 
              SEPARATOR ',') from customers 
         where customer_id in (customer_id));
   Return Result;
   END

when i call above function it returns comma seperated customerscodes like this '1','2','3','4'.

But I need to use the output Get_customerCodes functions result in where clause IN condition.

Test Case:

 select * from my_table where customer_code IN (Get_customerCodes(CAST('1002' AS SIGNED)));

Expectation:

On executing above query, mysql should give me the results based on the functions output.Actual query would look like bellow

select * from my_table where customer_code IN ('1','2','3','4');

Questions:

Upvotes: 0

Views: 898

Answers (2)

darma
darma

Reputation: 4747

I had a similar problem and solved it with the help of FIND_IN_SET:

SELECT * FROM my_table WHERE FIND_IN_SET(customer_code, Get_customerCodes(1002));

Upvotes: 1

wchiquito
wchiquito

Reputation: 16551

A couple of points to consider:

An option using 13.5 Prepared SQL Statement Syntax:

mysql> DROP TABLE IF EXISTS `my_table`, `customers`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP FUNCTION IF EXISTS `Get_customerCodes`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `customers`(
    ->   `customer_id` INT NOT NULL,
    ->   `customer_code` VARCHAR(2),
    ->   PRIMARY KEY(`customer_id`, `customer_code`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `my_table`(
    ->   `id` INT NOT NULL,
    ->   `customer_code` VARCHAR(2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION `Get_customerCodes`(`_customer_id` INT)
    -> RETURNS VARCHAR(500) CHARSET utf8
    ->   RETURN (
    ->     SELECT GROUP_CONCAT(CONCAT('\'', `customer_code`, '\'')
    ->            SEPARATOR ',')
    ->     FROM `customers`
    ->     WHERE `customer_id` IN (`_customer_id`)
    ->   );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `customers`
    ->   (`customer_id`, `customer_code`)
    -> VALUES
    ->   (1002, 1), (1002, 2),
    ->   (1002, 3), (1002, 4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `my_table`
    ->   (`id`, `customer_code`)
    -> VALUES
    ->   (1, 1), (2, 2),
    ->   (3, 3), (4, 4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SET @`query` := CONCAT('
    '> SELECT *
    '> FROM `my_table`
    '> WHERE `customer_code` IN (',
    -> `Get_customerCodes`(CAST('1002' AS SIGNED)), ')');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @`query` `to_execute`;
+----------------------------------------------------------------------+
| to_execute                                                           |
+----------------------------------------------------------------------+
| 
SELECT *
FROM `my_table`
WHERE `customer_code` IN ('1','2','3','4') |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE `stmt` FROM @`query`;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE `stmt`;
+----+---------------+
| id | customer_code |
+----+---------------+
|  1 | 1             |
|  2 | 2             |
|  3 | 3             |
|  4 | 4             |
+----+---------------+
4 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE `stmt`;
Query OK, 0 rows affected (0.00 sec)

See db-fiddle.

Upvotes: 0

Related Questions