I have two tables - "mailinglist" and "mailinglist_addresses".

MailingList
- id (PK)
- name
MailingListEmails
- mailinglist_id
- emailaddress
- PK is mailinglist_id AND emailaddress

I want to SELECT a result of
id,
name,
emailcount

WHERE emailcount is the number of emailaddresses for that
record's ID.

Can anyone help me with a join or somesuch? I still don't do
joins well.

Sanders,

Assuming your mailinglist_id column in the MailingListEmails table is a
foreign key referenceing MailingList, this should work for you:

select m.ID, m.Name, count(*) EmailCount
from MailingList m
inner join MailingListEmails e on m.ID = e.MailingList_ID
where e.MailingList_ID is null
group by m.ID, m.Name

-- Bill

"Sanders Kaufman" <bucky@kaufman.net> wrote in message
news:rBYNh.3283$Kd3.1748@newssvr27.news.prodigy.ne t...
>I have two tables - "mailinglist" and "mailinglist_addresses".
>
> MailingList
> - id (PK)
> - name
> MailingListEmails
> - mailinglist_id
> - emailaddress
> - PK is mailinglist_id AND emailaddress
>
> I want to SELECT a result of
> id,
> name,
> emailcount
>
> WHERE emailcount is the number of emailaddresses for that record's ID.
>
> Can anyone help me with a join or somesuch? I still don't do joins well.
>

Logically, yeah it's a foreign key. But there was nothing about
FK mentioned in the CREATE TABLE for it - that's okay, right?


AlterEgo wrote:
> Sanders,
>
> Assuming your mailinglist_id column in the MailingListEmails table is a
> foreign key referenceing MailingList, this should work for you:
>
> select m.ID, m.Name, count(*) EmailCount
> from MailingList m
> inner join MailingListEmails e on m.ID = e.MailingList_ID
> where e.MailingList_ID is null
> group by m.ID, m.Name
>
> -- Bill
>
> "Sanders Kaufman" <bucky@kaufman.net> wrote in message
> news:rBYNh.3283$Kd3.1748@newssvr27.news.prodigy.ne t...
>> I have two tables - "mailinglist" and "mailinglist_addresses".
>>
>> MailingList
>> - id (PK)
>> - name
>> MailingListEmails
>> - mailinglist_id
>> - emailaddress
>> - PK is mailinglist_id AND emailaddress
>>
>> I want to SELECT a result of
>> id,
>> name,
>> emailcount
>>
>> WHERE emailcount is the number of emailaddresses for that record's ID.
>>
>> Can anyone help me with a join or somesuch? I still don't do joins well.
>>
>
>

Sanders Kaufman <bucky@kaufman.net> wrote in
news:lOZNh.2791$u03.920@newssvr21.news.prodigy.net :

> Logically, yeah it's a foreign key. But there was nothing about
> FK mentioned in the CREATE TABLE for it - that's okay, right?

Correct, you do not need to explicitly specify them in table definitions,
but it might help you keep track across a large amount of tables...

Sanders,

You're good to go, a foreign key doesn't have to be declared for joins to
work. It serves to describe the relationships within the database and can
also enforce referential integrity if so declared.

-- Bill

"Sanders Kaufman" <bucky@kaufman.net> wrote in message
news:lOZNh.2791$u03.920@newssvr21.news.prodigy.net ...
> Logically, yeah it's a foreign key. But there was nothing about FK
> mentioned in the CREATE TABLE for it - that's okay, right?
>
>
> AlterEgo wrote:
>> Sanders,
>>
>> Assuming your mailinglist_id column in the MailingListEmails table is a
>> foreign key referenceing MailingList, this should work for you:
>>
>> select m.ID, m.Name, count(*) EmailCount
>> from MailingList m
>> inner join MailingListEmails e on m.ID = e.MailingList_ID
>> where e.MailingList_ID is null
>> group by m.ID, m.Name
>>
>> -- Bill
>>
>> "Sanders Kaufman" <bucky@kaufman.net> wrote in message
>> news:rBYNh.3283$Kd3.1748@newssvr27.news.prodigy.ne t...
>>> I have two tables - "mailinglist" and "mailinglist_addresses".
>>>
>>> MailingList
>>> - id (PK)
>>> - name
>>> MailingListEmails
>>> - mailinglist_id
>>> - emailaddress
>>> - PK is mailinglist_id AND emailaddress
>>>
>>> I want to SELECT a result of
>>> id,
>>> name,
>>> emailcount
>>>
>>> WHERE emailcount is the number of emailaddresses for that record's ID.
>>>
>>> Can anyone help me with a join or somesuch? I still don't do joins
>>> well.
>>>
>>

Thanks - that's what I needed to know.


AlterEgo wrote:
> Sanders,
>
> You're good to go, a foreign key doesn't have to be declared for joins to
> work. It serves to describe the relationships within the database and can
> also enforce referential integrity if so declared.
>
> -- Bill
>
> "Sanders Kaufman" <bucky@kaufman.net> wrote in message
> news:lOZNh.2791$u03.920@newssvr21.news.prodigy.net ...
>> Logically, yeah it's a foreign key. But there was nothing about FK
>> mentioned in the CREATE TABLE for it - that's okay, right?
>>
>>
>> AlterEgo wrote:
>>> Sanders,
>>>
>>> Assuming your mailinglist_id column in the MailingListEmails table is a
>>> foreign key referenceing MailingList, this should work for you:
>>>
>>> select m.ID, m.Name, count(*) EmailCount
>>> from MailingList m
>>> inner join MailingListEmails e on m.ID = e.MailingList_ID
>>> where e.MailingList_ID is null
>>> group by m.ID, m.Name
>>>
>>> -- Bill
>>>
>>> "Sanders Kaufman" <bucky@kaufman.net> wrote in message
>>> news:rBYNh.3283$Kd3.1748@newssvr27.news.prodigy.ne t...
>>>> I have two tables - "mailinglist" and "mailinglist_addresses".
>>>>
>>>> MailingList
>>>> - id (PK)
>>>> - name
>>>> MailingListEmails
>>>> - mailinglist_id
>>>> - emailaddress
>>>> - PK is mailinglist_id AND emailaddress
>>>>
>>>> I want to SELECT a result of
>>>> id,
>>>> name,
>>>> emailcount
>>>>
>>>> WHERE emailcount is the number of emailaddresses for that record's ID.
>>>>
>>>> Can anyone help me with a join or somesuch? I still don't do joins
>>>> well.
>>>>
>

On Mar 26, 5:26 pm, "AlterEgo" <altereg...@dslextreme.com> wrote:
> Sanders,
>
> Assuming your mailinglist_id column in the MailingListEmails table is a
> foreign key referenceing MailingList, this should work for you:
>
> select m.ID, m.Name, count(*) EmailCount
> from MailingList m
> inner join MailingListEmails e on m.ID = e.MailingList_ID
> where e.MailingList_ID is null
> group by m.ID, m.Name
>
> -- Bill
>
> "Sanders Kaufman" <b...@kaufman.net> wrote in message
>
> news:rBYNh.3283$Kd3.1748@newssvr27.news.prodigy.ne t...
>
> >I have two tables - "mailinglist" and "mailinglist_addresses".
>
> > MailingList
> > - id (PK)
> > - name
> > MailingListEmails
> > - mailinglist_id
> > - emailaddress
> > - PK is mailinglist_id AND emailaddress
>
> > I want to SELECT a result of
> > id,
> > name,
> > emailcount
>
> > WHERE emailcount is the number of emailaddresses for that record's ID.
>
> > Can anyone help me with a join or somesuch? I still don't do joins well.

Is there a functional difference between left join and inner join?
I'm trying to get something similar working, and I'm new to sql.

Chad

chadlupkes wrote:
> On Mar 26, 5:26 pm, "AlterEgo" <altereg...@dslextreme.com> wrote:
>> Sanders,
>>
>> Assuming your mailinglist_id column in the MailingListEmails table is a
>> foreign key referenceing MailingList, this should work for you:
>>
>> select m.ID, m.Name, count(*) EmailCount
>> from MailingList m
>> inner join MailingListEmails e on m.ID = e.MailingList_ID
>> where e.MailingList_ID is null
>> group by m.ID, m.Name
>>
>> -- Bill
>>
>> "Sanders Kaufman" <b...@kaufman.net> wrote in message
>>
>> news:rBYNh.3283$Kd3.1748@newssvr27.news.prodigy.ne t...
>>
>>> I have two tables - "mailinglist" and "mailinglist_addresses".
>>> MailingList
>>> - id (PK)
>>> - name
>>> MailingListEmails
>>> - mailinglist_id
>>> - emailaddress
>>> - PK is mailinglist_id AND emailaddress
>>> I want to SELECT a result of
>>> id,
>>> name,
>>> emailcount
>>> WHERE emailcount is the number of emailaddresses for that record's ID.
>>> Can anyone help me with a join or somesuch? I still don't do joins well.
>
> Is there a functional difference between left join and inner join?
> I'm trying to get something similar working, and I'm new to sql.
>
> Chad
>

Chad,

Yes. INNER JOIN returns rows only when there is a match in the JOIN.
LEFT JOIN returns all rows on the left side and any matching rows on the
right side. If there are no matching rows the right side values are
filled in with nulls.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================