Re: [sqlite] Simple Outer Join question?

[Leo Freitag]

Hi Igor,

thanks, works fine!
Where do I find a tutorial that deals with 'IN', 'NOT IN' and subquerys 
in general?

Leo

Igor Tandetnik schrieb:
> "Kees Nuyt"  wrote in
> message news:8u3m151rqbbel40ilsvaatqmfhcnhsj...@dim53.demon.nl
>   
>> On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag
>>  wrote:
>> 
>>> I have a table 'person' and a table 'group'. Every person can join
>>> none, one or more groups.
>>> No I want to select all persons except those who are member in group
>>> 1. - Sounds simple, but not for me.
>>>   
>> This is an n:m relationship.
>> If group has more attributes (columns) than just its number,
>> you need a third table: person_group.
>> Then join person with person_group where group_id != 1;
>> 
>
> That would also pick people that are both in group 1 and group 2. You 
> would need something like
>
> select * from person
> where person_id not in (
> select person_id from person_group where group_id=1);
>
> 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

Re: [sqlite] Simple Outer Join question?

[Igor Tandetnik]

"Kees Nuyt"  wrote in
message news:8u3m151rqbbel40ilsvaatqmfhcnhsj...@dim53.demon.nl
> On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag
>  wrote:
>> I have a table 'person' and a table 'group'. Every person can join
>> none, one or more groups.
>> No I want to select all persons except those who are member in group
>> 1. - Sounds simple, but not for me.
>
> This is an n:m relationship.
> If group has more attributes (columns) than just its number,
> you need a third table: person_group.
> Then join person with person_group where group_id != 1;

That would also pick people that are both in group 1 and group 2. You 
would need something like

select * from person
where person_id not in (
select person_id from person_group where group_id=1);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple Outer Join question?

[Kees Nuyt]

On Mon, 25 May 2009 23:14:50 +0200, Leo Freitag
 wrote:

>Hallo,
>
>I have a table 'person' and a table 'group'. Every person can join none, 
>one or more groups.
>No I want to select all persons except those who are member in group 1. 
>- Sounds simple, but not for me.

This is an n:m relationship.
If group has more attributes (columns) than just its number,
you need a third table: person_group.
Then join person with person_group where group_id != 1;

The person_group table could be called membership, if you
like.

>Thanks in advance
>Leo
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Outer Join question?

[Leo Freitag]

Hallo,

I have a table 'person' and a table 'group'. Every person can join none, 
one or more groups.
No I want to select all persons except those who are member in group 1. 
- Sounds simple, but not for me.

Thanks in advance
Leo



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users