Hallo,
I thought it is a trivial problem, but I don't find the solution...
I have to tables p and g, p are people which can be members of groups
stored in g. It's a n-to-n relation, so I made a third table r which
brings together the members and groups. Of course that is done by
CREATE table p (p_id integer, name string)
CREATE table g (g_id integer, group string)
CREATE table r (m_id integer, g_id integer)
There are foreign keys, but we don't worry about that here.
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.
Is it a matter of a "simple" SELECTs with joined tables? Have I to think
about subqueries? Or even Compund SELECTs (UNION, INTERSECT)?
It would be great to get some hints!
Ulrich
--
Ulrich Goebel
Paracelsusstr. 120, 53177 Bonn
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
