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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
