Basic where clause ?

Hi there,

I have a very simple question I guess:

I have two tables, let's say "Family" and "Person" and a "Family"
contains one or more "Person".

If I want to select every Family for which (at least) a Person is called
"Peter", I will write something like this:

select distinct(f.idFamily) from Family as f join Person as p on
p.idFamily=f.idFamily where p.name like 'Peter';

But if now, I want every Family in which no one is called "Peter", I
don't know how to write it. It seems so basic, but I don't even know
where to seek for help on WWW.

Any tips ?

--
Hugo

Hugo wrote:
> Hi there,
>
> I have a very simple question I guess:
>
> I have two tables, let's say "Family" and "Person" and a "Family"
> contains one or more "Person".
>
> If I want to select every Family for which (at least) a Person is
> called "Peter", I will write something like this:
>
> select distinct(f.idFamily) from Family as f join Person as p on
> p.idFamily=f.idFamily where p.name like 'Peter';
>
> But if now, I want every Family in which no one is called "Peter", I
> don't know how to write it. It seems so basic, but I don't even know
> where to seek for help on WWW.

Seems an Outer Join / where NULL statement.
Something like (untested):

SELECT x.family
FROM (
SELECT f.idFamily AS 'family', p.name AS 'name'
FROM family f
LEFT OUTER JOIN person p ON p.idFamily = f.idFamily
AND p.name LIKE 'peter'
) x
WHERE x.name IS NULL

The trick is the LEFT _OUTER_ JOIN, where the requirement is that
person.name a NOT NULL field is.

Grtz,
--
Rik Wasmus

--
Rik Wasmus

Rik wrote:
> Hugo wrote:
>> Hi there,
>>
>> I have a very simple question I guess:
>>
>> I have two tables, let's say "Family" and "Person" and a "Family"
>> contains one or more "Person".
>>
>> If I want to select every Family for which (at least) a Person is
>> called "Peter", I will write something like this:
>>
>> select distinct(f.idFamily) from Family as f join Person as p on
>> p.idFamily=f.idFamily where p.name like 'Peter';
>>
>> But if now, I want every Family in which no one is called "Peter", I
>> don't know how to write it. It seems so basic, but I don't even know
>> where to seek for help on WWW.
>
> Seems an Outer Join / where NULL statement.
> Something like (untested):
>
> SELECT x.family
> FROM (
> SELECT f.idFamily AS 'family', p.name AS 'name'
> FROM family f
> LEFT OUTER JOIN person p ON p.idFamily = f.idFamily
> AND p.name LIKE 'peter'
> ) x
> WHERE x.name IS NULL
>

Well, I'm curious, did it work?

Grtz,
--
Rik Wasmus

Rik wrote :

>> SELECT x.family
>> FROM (
>> SELECT f.idFamily AS 'family', p.name AS 'name'
>> FROM family f
>> LEFT OUTER JOIN person p ON p.idFamily = f.idFamily
>> AND p.name LIKE 'peter'
>> ) x
>> WHERE x.name IS NULL
>>
>
> Well, I'm curious, did it work?

Sorry to left that unanswered, but I was on holidays last days.

Well in fact I didn't test it yet, but I guess it will work.
Unfortunately, this query is part of a bigger query so I don't know if
it's not too complicated to get good perfs.

Maybe I will need to redifined better my needs, or try some other way to
filter my data. For now I'm quite on standby, but anyway thanks for your
answer !

--
Hugo