CODEWITHSUNDEEP
mysql
Ben Holness
Ben Holness

Reputation: 2707

MySQL : Using a function output with IN

I have a MySQL function "GetChildren(id)", which returns a comma separated list of ids that are children of the id passed in to the function.

I would like to use the output of that function as follows:

SELECT * FROM table WHERE id IN (GetChildren(36))

and if id 36 had children ids 37,38 and 39, then I would get those three rows back.

What happens, however, is that I only get the first id.

SQL fiddle seemed to have difficulties with functions, but here is a simplified example that demonstrates the problem:

CREATE TABLE test (
  `ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO test (ID) VALUES (1),(2),(3),(4);

DELIMITER $$

DROP FUNCTION IF EXISTS `TestFN` $$
CREATE FUNCTION `TestFN` (GivenID  VARCHAR(1024)) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
  RETURN '2,3,4';
END $$
DELIMITER ;

Once setup, if I run SELECT TestFN(1), I get back 2,3,4 as expected.

If I run SELECT ID FROM test WHERE ID IN (TestFN1), I get back just one row, with ID 2. The same happens with SELECT ID FROM test WHERE ID = (TestFN1)

Is there a way I can use the output of this function for use as if I had typed the IDs in an ID IN (...) clause?

Upvotes: 0

Views: 28

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

I think MySQL would need to support table-valued functions for you to use one in this way. Otherwise, IN is behaving as expected, someInt IN (astring) casts the IN list members to ints, evaluating in your case to ID IN (2)

However, you can probably use FIND_IN_SET in a manner similar to what you are trying.

Something like FIND_IN_SET(ID, GetChildren(36)) > 0

Upvotes: 2

Related Questions