Mysql functions output as part of where condition in clause

Question

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:

Is it possible to use mysql function output as part of where condition IN clause.
If possible provide an example. If not possible give me an alternative solution.

RETURNS varchar (500) <> Declare Result VARCHAR (1000); Caution, your result may be truncated. You can use a 13.5 Prepared SQL Statement Syntax. — wchiquito
– wchiquito, Commented Jul 19, 2017 at 15:03

darma · Accepted Answer · 2020-04-20 19:38:17Z

1

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));

answered Apr 20, 2020 at 19:38

darma

4,7571 gold badge27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wchiquito · Accepted Answer · 2017-07-19 15:49:09Z

A couple of points to consider:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable,, see 12.19.1 Aggregate (GROUP BY) Function Descriptions :: GROUP_CONCAT.
Avoid naming the parameters of the function as columns of your tables, see C.1 Restrictions on Stored Programs :: Name Conflicts within Stored Routines.

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.

Collectives™ on Stack Overflow

Mysql functions output as part of where condition in clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related