Finding Records w/o "Children"

[Mark]

I need an aggregate SQL Statement to count the number of "A" records that
do not have a corresponding "B" record, i.e., each "B" record has a foreign
key pointing back to an "A" record. The "B" records are associative and
connect a many-to-many between "A" and "C" I used to know how to do this
but I'm getting old and forgetful and can't find anything in my references:


A----------<B>----------C

How many A records have no corresponding B records.

I'm sure this is ridiculously simple.

thanks

[David Satz]

if I understand the question, here is an answering using SQL Server's pub
db:

select *
from authors a
where not exists ( select 1
from titleauthor ta
where ta.au_id = a.au_id )
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Mark" <marktaylor@copebhs.com> wrote in message
news:3c7ff60c$1@10.1.10.29...
>
> I need an aggregate SQL Statement to count the number of "A" records that
> do not have a corresponding "B" record, i.e., each "B" record has a
foreign
> key pointing back to an "A" record. The "B" records are associative and
> connect a many-to-many between "A" and "C" I used to know how to do this
> but I'm getting old and forgetful and can't find anything in my
references:
>
>
> A----------<B>----------C
>
> How many A records have no corresponding B records.
>
> I'm sure this is ridiculously simple.
>
> thanks
>

[Mark]

David, I do believe this is all, perhaps even more than I had hoped for (there
was a more complex spin on what I was after but didn't want to get wordy
and figured I could figure it out if I had a good starting point) This seems
to give me everything I need. Thanks a bunch!

Best to ya!

Mark

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>if I understand the question, here is an answering using SQL Server's pub
>db:
>
>select *
>from authors a
>where not exists ( select 1
> from titleauthor ta
> where ta.au_id = a.au_id )
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS
}
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"Mark" <marktaylor@copebhs.com> wrote in message
>news:3c7ff60c$1@10.1.10.29...
>>
>> I need an aggregate SQL Statement to count the number of "A" records that
>> do not have a corresponding "B" record, i.e., each "B" record has a
>foreign
>> key pointing back to an "A" record. The "B" records are associative and
>> connect a many-to-many between "A" and "C" I used to know how to do this
>> but I'm getting old and forgetful and can't find anything in my
>references:
>>
>>
>> A----------<B>----------C
>>
>> How many A records have no corresponding B records.
>>
>> I'm sure this is ridiculously simple.
>>
>> thanks
>>
>
>

[hylton]

Depending on your indexes and the size of the tables...it might be more efficient
to do a left join (master to detail) and pull where the 'join field' in the
detail table is NULL...might run faster...just depends, try both methods...

Chris

"Mark" <mark@copebhs.com> wrote:
>
>David, I do believe this is all, perhaps even more than I had hoped for
(there
>was a more complex spin on what I was after but didn't want to get wordy
>and figured I could figure it out if I had a good starting point) This
seems
>to give me everything I need. Thanks a bunch!
>
>Best to ya!
>
>Mark
>
>"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>>if I understand the question, here is an answering using SQL Server's pub
>>db:
>>
>>select *
>>from authors a
>>where not exists ( select 1
>> from titleauthor ta
>> where ta.au_id = a.au_id )
>>--
>>HTH,
>>David Satz
>>Principal Web Engineer
>>Hyperion Solutions
>>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
VSS
>}
>>(Please reply to group only - emails answered rarely)
>>-----------------------------------------------------------------
>>"Mark" <marktaylor@copebhs.com> wrote in message
>>news:3c7ff60c$1@10.1.10.29...
>>>
>>> I need an aggregate SQL Statement to count the number of "A" records
that
>>> do not have a corresponding "B" record, i.e., each "B" record has a
>>foreign
>>> key pointing back to an "A" record. The "B" records are associative
and
>>> connect a many-to-many between "A" and "C" I used to know how to do this
>>> but I'm getting old and forgetful and can't find anything in my
>>references:
>>>
>>>
>>> A----------<B>----------C
>>>
>>> How many A records have no corresponding B records.
>>>
>>> I'm sure this is ridiculously simple.
>>>
>>> thanks
>>>
>>
>>
>

[Jo Willems]

How about set operators?
select count(*) from (Select key of table a minus select foreignkey of B)

?


"hylton" <cchylton@hotmail.com> wrote:
>
>Depending on your indexes and the size of the tables...it might be more
efficient
>to do a left join (master to detail) and pull where the 'join field' in
the
>detail table is NULL...might run faster...just depends, try both methods...
>
>Chris
>
>"Mark" <mark@copebhs.com> wrote:
>>
>>David, I do believe this is all, perhaps even more than I had hoped for
>(there
>>was a more complex spin on what I was after but didn't want to get wordy
>>and figured I could figure it out if I had a good starting point) This
>seems
>>to give me everything I need. Thanks a bunch!
>>
>>Best to ya!
>>
>>Mark
>>
>>"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>>>if I understand the question, here is an answering using SQL Server's
pub
>>>db:
>>>
>>>select *
>>>from authors a
>>>where not exists ( select 1
>>> from titleauthor ta
>>> where ta.au_id = a.au_id )
>>>--
>>>HTH,
>>>David Satz
>>>Principal Web Engineer
>>>Hyperion Solutions
>>>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
>VSS
>>}
>>>(Please reply to group only - emails answered rarely)
>>>-----------------------------------------------------------------
>>>"Mark" <marktaylor@copebhs.com> wrote in message
>>>news:3c7ff60c$1@10.1.10.29...
>>>>
>>>> I need an aggregate SQL Statement to count the number of "A" records
>that
>>>> do not have a corresponding "B" record, i.e., each "B" record has a
>>>foreign
>>>> key pointing back to an "A" record. The "B" records are associative
>and
>>>> connect a many-to-many between "A" and "C" I used to know how to do
this
>>>> but I'm getting old and forgetful and can't find anything in my
>>>references:
>>>>
>>>>
>>>> A----------<B>----------C
>>>>
>>>> How many A records have no corresponding B records.
>>>>
>>>> I'm sure this is ridiculously simple.
>>>>
>>>> thanks
>>>>
>>>
>>>
>>
>

[Joe \Nuke Me Xemu\ Foster]

"Mark" <marktaylor@copebhs.com> wrote in message <news:3c7ff60c$1@10.1.10.29>...

> I need an aggregate SQL Statement to count the number of "A" records that
> do not have a corresponding "B" record, i.e., each "B" record has a foreign
> key pointing back to an "A" record. The "B" records are associative and
> connect a many-to-many between "A" and "C" I used to know how to do this
> but I'm getting old and forgetful and can't find anything in my references:
>
>
> A----------<B>----------C
>
> How many A records have no corresponding B records.

select a.*
from a
where not exists (select * from b where a.id = b.id)

select a.*
from a left join b on a.id = b.id
where b.id is null

Try them both. Modern query optimizers should recognize that
both are equivalent and they should therefore execute equally
quickly, but you never know...

--
Joe Foster <mailto:jlfoster%40znet.com> Sign the Check! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!