CODEWITHSUNDEEP
mysql
Neveen
Neveen

Reputation: 107

mysql function return list of data

I would like to write a mysql function that takes an integer as input and that function execute a select statements "select id from table_name" then i want that function return the result of select statements.

then

in query i want to do smthing like:

select id,name from table_name where id in (call function here that returns a list of ids).

Is that valid in mysql ??

Thanks in Advance

Upvotes: 4

Views: 11940

Answers (5)

CSTobey
CSTobey

Reputation: 1511

If you really need to use a function, you can: 

CREATE FUNCTION my_func (in_id INT(11), value INT(11)) RETURNS INT(11) DETERMINISTIC 
RETURN in_id IN (SELECT id FROM table_name WHERE id = value);

and you would call it with this:

SELECT id,name from table_name where myfunc(id, 1);

It is generally slow because MySQL can't optimize its use, but does the job.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425371

No, you cannot do this in MySQL.

A MySQL procedure that returns a resultset cannot be used in a subquery.

If your function is just a number of SELECT statements, you can do something like this instead:

SELECT  id, name
FROM    mytable1
WHERE   id IN
        (
        SELECT  id
        FROM    mytable2
        WHERE   value = @myinteger
        UNION ALL
        SELECT  id
        FROM    mytable2
        WHERE   value = @myinteger
        UNION ALL
        …
        )

or, which is more efficient,

SELECT  id, name
FROM    mytable1 t1
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    mytable2 t2
        WHERE   t2.id = t1.id
                AND t2.value = @myinteger
        UNION ALL
        SELECT  NULL
        FROM    mytable3 t2
        WHERE   t3.id = t1.id
                AND t3.value = @myinteger
        UNION ALL
        …
        )

Upvotes: 1

nsmyself
nsmyself

Reputation: 3565

Sure. You can use a Stored Procedure, a UDF or even a subquery.

Have a look:

  1. MySQL 5.5 Reference Manual - Stored Procedures
  2. MySQL 5.5 Reference Manual - Subqueries
  3. MySQL Stored Procedures: Part 1

Upvotes: 0

Julius A
Julius A

Reputation: 39622

You dont need a function, you can use a subquery.

E.g.

select id,name from table_name where id in (select someIds from othertable)

Upvotes: 0

Salil
Salil

Reputation: 47482

Yes you can use subqueries in MySql

something like following

select id,name from table_name1 
               where id in (select DISTINCT(table_name1_id) from table_name2)

Upvotes: 1

Related Questions