In Igor's post below, what is the meaning of the colon in front of mypid? RobR
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, November 06, 2013 8:45 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Groups and members On 11/6/2013 6:55 AM, Ulrich Goebel wrote: > Now my problem: For a given person (that is a given p_id) I would like > to get a list of all (!) groups, marked wether p_id is a member of it > or not. If p_id=1 and this person is member of groups 4, 8 and 9, > > SELECT g_id, p_id, p_is_member_of_g ... > > should give > > g_id p_id is_member > 4 1 yes > 8 1 yes > 9 1 yes > 1 1 no > 2 1 no > 3 1 no > > Note that the groups with p_id in it come first and are sorted, then > come the groups with p_id not in it, sorted too. select g_id, :mypid, (case when r.p_id is null then 'no' else 'yes' end) is_member from g left join r on (g.g_id = r.g_id and r.m_id = :mypid); order by (r.p_id is null), g_id; On an unrelated note, your text fields should have the type of "text", not "string". The latter has no special meaning to SQLite, but the former does. For details, see http://sqlite.org/datatype3.html -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
