CODEWITHSUNDEEP
mysqlfunction
tttpapi
tttpapi

Reputation: 887

MySQL SELECT WHERE id IN function()

I have a function that returns VARCHAR.

It will be always empty string "" OR single ID "1" OR IDs "1,2,3,4,5".

Then I have this query:

SELECT id, title FROM category WHERE id IN getIds(1);

But it returns only first item because it is as a string, eg. 1, instead if 1,2,3.

Is there a way to tell MySQL it should use it not as a string?

Upvotes: 0

Views: 734

Answers (1)

Tom
Tom

Reputation: 2390

Since MySQL does not support table-valued functions. You have 2 choices:

1) Use FIND_IN_SET https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set AND http://www.w3resource.com/mysql/string-functions/mysql-find_in_set-function.php "This function returns the location of a string given in the first argument of the function, in a comma-separated list in a string given in the second argument. The first element of the list is 1. A 0 is returned if the string is not found in the set. It returns NULL if either argument is NULL."

SELECT id, title FROM category WHERE FIND_IN_SET(id, getIds(1)) > 0

2) Populate a temp table and use it with the IN operator

SELECT id, title FROM category WHERE id IN (SELECT Id FROM myTempTable)

Approach #1 seems more appropriate for this case.

Upvotes: 1

Related Questions