Function call reult in a WHERE-IN clause

[Cantwell, Bryan]

I have a function that I built that returns a string that is really a
comma separated list of values (below). I would like to use that
returned value in an IN clause in sql. :

select * from hosts where hostid in (getHosts(1001000000004));

The function:
CREATE FUNCTION getUserHosts(userID BIGINT(20) UNSIGNED)
  RETURNS varchar(4096) CHARSET latin1
BEGIN
  DECLARE hosts VARCHAR (4096);
  SELECT
    GROUP_CONCAT(DISTINCT h.hostid)
  INTO
    hosts
  FROM
    hosts h LEFT JOIN hosts_groups hg ON hg.hostid = h.hostid
    LEFT JOIN groups g ON g.groupid = hg.groupid
    LEFT JOIN rights r ON r.id = g.groupid AND r.type = 1
    LEFT JOIN users_groups ug ON ug.usrgrpid = r.groupid
    LEFT JOIN nodes n ON getNodeFromID(h.hostid) = n.nodeid
  WHERE
    ug.userid = userID
    AND r.permission = 3
    AND h.status <> 4
  ORDER BY
    h.hostid;
  RETURN hosts;
END

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org