Re: [sqlite] Groups and members

2013-11-06 Thread Ulrich Goebel
That gives me the solution - thank You! Am 06.11.2013 14:45, schrieb Igor Tandetnik: 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

Re: [sqlite] Groups and members

2013-11-06 Thread Dominique Devienne
On Wed, Nov 6, 2013 at 3:53 PM, Igor Tandetnik wrote: > On 11/6/2013 8:50 AM, Dominique Devienne wrote: > >> On Wed, Nov 6, 2013 at 2:45 PM, Igor Tandetnik >> wrote: >> >> select g_id, :mypid, >>>(case when r.p_id is null then 'no' else 'yes' end)

Re: [sqlite] Groups and members

2013-11-06 Thread Igor Tandetnik
On 11/6/2013 8:50 AM, Dominique Devienne wrote: On Wed, Nov 6, 2013 at 2:45 PM, Igor Tandetnik wrote: 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

Re: [sqlite] Groups and members

2013-11-06 Thread Dominique Devienne
On Wed, Nov 6, 2013 at 2:45 PM, Igor Tandetnik wrote: > 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; > That's what I said! :) (our

Re: [sqlite] Groups and members

2013-11-06 Thread Dominique Devienne
On Wed, Nov 6, 2013 at 12:55 PM, Ulrich Goebel wrote: > 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! Sounds like a job for

Re: [sqlite] Groups and members

2013-11-06 Thread Igor Tandetnik
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 ...

[sqlite] Groups and members

2013-11-06 Thread Ulrich Goebel
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