2009/9/16 Marcel Strittmatter <[email protected]>:
> Hi
>
> I implemented a custom function that returns a comma separated list of
> primary keys as a string by making recursive queries. This works well
> if I don't use subqueries. But I like to use subqueries like this
>
> SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));
>
> Below some example data I used to test my custom function...
>
> Is it possible to implement a custom function where the result can be
> used in a subquery like above?
Yes - you seem to have achieved that.
>
> Regards
> Marcel
>
>
> Here a dump of an example database:
>
> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
> INSERT INTO "users" VALUES(1,'Marge');
> INSERT INTO "users" VALUES(2,'Homer');
> INSERT INTO "users" VALUES(3,'Lisa');
> INSERT INTO "users" VALUES(4,'Bart');
> INSERT INTO "users" VALUES(5,'Maggie');
> CREATE TABLE relations (parent INTEGER, child INTEGER);
> INSERT INTO "relations" VALUES(1,3);
> INSERT INTO "relations" VALUES(1,4);
> INSERT INTO "relations" VALUES(1,5);
> INSERT INTO "relations" VALUES(2,3);
> INSERT INTO "relations" VALUES(2,4);
>
> And here the output of my custom function (parents), which is similar
> to the builtin function group_concat():
>
> sqlite> SELECT name,parents('relations', id) FROM users WHERE id = 3;
> name parents('relations', id)
> ---------- ------------------------
> Lisa 1,2
>
> But the following query returns nothing
>
> SELECT name FROM users WHERE id IN (SELECT parents('relations', id)
> FROM users WHERE id = 3);
>
> because the result of the custom function is a string ("1,2")...
Indeed. But your custom function is working just fine.
Why not just use
SELECT name FROM users WHERE id IN (SELECT parent from relations WHERE
child = 3);
??
Regards,
Simon
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
