mysql function return list of data

Question

Hello,

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

Answer 1

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)

Answer 2

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)

Answer 3

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

Answer 4

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