help with santax

Given the following table setup

Table
column1
column2

forumCategory
ID
name

forum
ID
name
forumCategoryID

ForumTopics
ID
forumID

ForumTopicMsg
ID
topicID
dateEntered

I am attempting to create a query that gives a complete overview which will
show all the forumCategory's, followed by all its forums while giving a
count of all topics belonging to that forum, counting all msgs belonging to
that topic, and the date of the last msg belonging to that topic.

forumCategory.name
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forumCategory.name
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forumCategory.name
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)
forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
Newest(forumTopicMsg.dateEntered)

I cannot figure out how to maintain a relationship across all table and
still returning all info. This is further compounded by the fact that I
need to also return the forum.name even if no topic(s) yet exist for that
forum. So my attempts at using a group by clause have failed. I would
appreciate any help or insight anyone could offer.I think this will do it:

select
ForumCategory = fc.name,
ForumName = f.name,
NumberOfTopics = count(ft.id),
NumberOfMsgs = count(ftm.id),
LastUpdate = (Select max(dateentered) from ForumTopicMsg where topicid =
ft.id)
from forumCategory as fc
inner join forum as f
on (fc.id=f.forumCategoryID)
inner join forumTopics a ft
on (f.ID=ft.forumID)
inner join forumTopicMsg as ftm
on (ft.id=ftm.topicid)
group by fc.name, f.name

But a couple other things. You should really keep your foreign key
information consistent, there should be only one "id" that points to a
primary key of another table. For example:

forumCategory
fc_id
fc_name

forum
forum_id
fc_id
forum_name

You can use whatever naming standards you want (like I used both a fc_ and a
<tablename>_ type prefix) - that doesn't matter, so long as it's clear - but
the much bigger issue is that if you call forumCategory.fc_id - it needs to
be called fc_id everywhere else too.

HTH

"Bryan Martin" <spam@.myplaceinspace.com> wrote in message
news:9yx3e.13042$JL2.385089@.twister.southeast.rr.c om...
> Given the following table setup
> Table
> column1
> column2
> forumCategory
> ID
> name
> forum
> ID
> name
> forumCategoryID
> ForumTopics
> ID
> forumID
> ForumTopicMsg
> ID
> topicID
> dateEntered
> I am attempting to create a query that gives a complete overview which
> will show all the forumCategory's, followed by all its forums while giving
> a count of all topics belonging to that forum, counting all msgs belonging
> to that topic, and the date of the last msg belonging to that topic.
> forumCategory.name
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forumCategory.name
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forumCategory.name
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> Newest(forumTopicMsg.dateEntered)
> I cannot figure out how to maintain a relationship across all table and
> still returning all info. This is further compounded by the fact that I
> need to also return the forum.name even if no topic(s) yet exist for that
> forum. So my attempts at using a group by clause have failed. I would
> appreciate any help or insight anyone could offer.|||To add to RCS's post

Look at using an OUTER JOIN for those tables where rows may not exit.
Such as

select
ForumCategory = fc.name,
ForumName = f.name,
NumberOfTopics = count(ft.id),
NumberOfMsgs = count(ftm.id),
LastUpdate = (Select max(dateentered) from ForumTopicMsg where topicid
= ft.id)
from forumCategory as fc
inner join forum as f on (fc.id=f.forumCategoryID)
LEFT JOIN forumTopics a ft on (f.ID=ft.forumID)
LEFT JOIN forumTopicMsg as ftm on (ft.id=ftm.topicid)
group by fc.name, f.name

More information on the types of joins is available in Books online.

John

RCS wrote:
> I think this will do it:
> select
> ForumCategory = fc.name,
> ForumName = f.name,
> NumberOfTopics = count(ft.id),
> NumberOfMsgs = count(ftm.id),
> LastUpdate = (Select max(dateentered) from ForumTopicMsg where
topicid =
> ft.id)
> from forumCategory as fc
> inner join forum as f
> on (fc.id=f.forumCategoryID)
> inner join forumTopics a ft
> on (f.ID=ft.forumID)
> inner join forumTopicMsg as ftm
> on (ft.id=ftm.topicid)
> group by fc.name, f.name
> But a couple other things. You should really keep your foreign key
> information consistent, there should be only one "id" that points to
a
> primary key of another table. For example:
> forumCategory
> fc_id
> fc_name
> forum
> forum_id
> fc_id
> forum_name
> You can use whatever naming standards you want (like I used both a
fc_ and a
> <tablename>_ type prefix) - that doesn't matter, so long as it's
clear - but
> the much bigger issue is that if you call forumCategory.fc_id - it
needs to
> be called fc_id everywhere else too.
> HTH
>
> "Bryan Martin" <spam@.myplaceinspace.com> wrote in message
> news:9yx3e.13042$JL2.385089@.twister.southeast.rr.c om...
> > Given the following table setup
> > Table
> > column1
> > column2
> > forumCategory
> > ID
> > name
> > forum
> > ID
> > name
> > forumCategoryID
> > ForumTopics
> > ID
> > forumID
> > ForumTopicMsg
> > ID
> > topicID
> > dateEntered
> > I am attempting to create a query that gives a complete overview
which
> > will show all the forumCategory's, followed by all its forums while
giving
> > a count of all topics belonging to that forum, counting all msgs
belonging
> > to that topic, and the date of the last msg belonging to that
topic.
> > forumCategory.name
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forumCategory.name
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forumCategory.name
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
> > Newest(forumTopicMsg.dateEntered)
> > I cannot figure out how to maintain a relationship across all table
and
> > still returning all info. This is further compounded by the fact
that I
> > need to also return the forum.name even if no topic(s) yet exist
for that
> > forum. So my attempts at using a group by clause have failed. I
would
> > appreciate any help or insight anyone could offer.|||Thx RCS and John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1112472695.860127.107770@.o13g2000cwo.googlegr oups.com...
> To add to RCS's post
> Look at using an OUTER JOIN for those tables where rows may not exit.
> Such as
> select
> ForumCategory = fc.name,
> ForumName = f.name,
> NumberOfTopics = count(ft.id),
> NumberOfMsgs = count(ftm.id),
> LastUpdate = (Select max(dateentered) from ForumTopicMsg where topicid
> = ft.id)
> from forumCategory as fc
> inner join forum as f on (fc.id=f.forumCategoryID)
> LEFT JOIN forumTopics a ft on (f.ID=ft.forumID)
> LEFT JOIN forumTopicMsg as ftm on (ft.id=ftm.topicid)
> group by fc.name, f.name
>
> More information on the types of joins is available in Books online.
> John
> RCS wrote:
>> I think this will do it:
>>
>> select
>> ForumCategory = fc.name,
>> ForumName = f.name,
>> NumberOfTopics = count(ft.id),
>> NumberOfMsgs = count(ftm.id),
>> LastUpdate = (Select max(dateentered) from ForumTopicMsg where
> topicid =
>> ft.id)
>> from forumCategory as fc
>> inner join forum as f
>> on (fc.id=f.forumCategoryID)
>> inner join forumTopics a ft
>> on (f.ID=ft.forumID)
>> inner join forumTopicMsg as ftm
>> on (ft.id=ftm.topicid)
>> group by fc.name, f.name
>>
>> But a couple other things. You should really keep your foreign key
>> information consistent, there should be only one "id" that points to
> a
>> primary key of another table. For example:
>>
>> forumCategory
>> fc_id
>> fc_name
>>
>> forum
>> forum_id
>> fc_id
>> forum_name
>>
>> You can use whatever naming standards you want (like I used both a
> fc_ and a
>> <tablename>_ type prefix) - that doesn't matter, so long as it's
> clear - but
>> the much bigger issue is that if you call forumCategory.fc_id - it
> needs to
>> be called fc_id everywhere else too.
>>
>> HTH
>>
>>
>> "Bryan Martin" <spam@.myplaceinspace.com> wrote in message
>> news:9yx3e.13042$JL2.385089@.twister.southeast.rr.c om...
>> > Given the following table setup
>>> > Table
>> > column1
>> > column2
>>> > forumCategory
>> > ID
>> > name
>>> > forum
>> > ID
>> > name
>> > forumCategoryID
>>> > ForumTopics
>> > ID
>> > forumID
>>> > ForumTopicMsg
>> > ID
>> > topicID
>> > dateEntered
>>> > I am attempting to create a query that gives a complete overview
> which
>> > will show all the forumCategory's, followed by all its forums while
> giving
>> > a count of all topics belonging to that forum, counting all msgs
> belonging
>> > to that topic, and the date of the last msg belonging to that
> topic.
>>> > forumCategory.name
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forumCategory.name
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forumCategory.name
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>> > Newest(forumTopicMsg.dateEntered)
>>> > I cannot figure out how to maintain a relationship across all table
> and
>> > still returning all info. This is further compounded by the fact
> that I
>> > need to also return the forum.name even if no topic(s) yet exist
> for that
>> > forum. So my attempts at using a group by clause have failed. I
> would
>> > appreciate any help or insight anyone could offer.
>>|||Woops - yep, that's right.. with my way, it would only bring back rows if
there were messages in each topic..

Using a left outer join, will bring back the forum level data, and just have
0's for the number of topics, number of msgs and lastupdate should be
blank..

"Bryan Martin" <spam@.myplaceinspace.com> wrote in message
news:JBF3e.15989$JL2.433215@.twister.southeast.rr.c om...
> Thx RCS and John
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1112472695.860127.107770@.o13g2000cwo.googlegr oups.com...
>> To add to RCS's post
>>
>> Look at using an OUTER JOIN for those tables where rows may not exit.
>> Such as
>>
>> select
>> ForumCategory = fc.name,
>> ForumName = f.name,
>> NumberOfTopics = count(ft.id),
>> NumberOfMsgs = count(ftm.id),
>> LastUpdate = (Select max(dateentered) from ForumTopicMsg where topicid
>> = ft.id)
>> from forumCategory as fc
>> inner join forum as f on (fc.id=f.forumCategoryID)
>> LEFT JOIN forumTopics a ft on (f.ID=ft.forumID)
>> LEFT JOIN forumTopicMsg as ftm on (ft.id=ftm.topicid)
>> group by fc.name, f.name
>>
>>
>> More information on the types of joins is available in Books online.
>>
>> John
>>
>> RCS wrote:
>>> I think this will do it:
>>>
>>> select
>>> ForumCategory = fc.name,
>>> ForumName = f.name,
>>> NumberOfTopics = count(ft.id),
>>> NumberOfMsgs = count(ftm.id),
>>> LastUpdate = (Select max(dateentered) from ForumTopicMsg where
>> topicid =
>>> ft.id)
>>> from forumCategory as fc
>>> inner join forum as f
>>> on (fc.id=f.forumCategoryID)
>>> inner join forumTopics a ft
>>> on (f.ID=ft.forumID)
>>> inner join forumTopicMsg as ftm
>>> on (ft.id=ftm.topicid)
>>> group by fc.name, f.name
>>>
>>> But a couple other things. You should really keep your foreign key
>>> information consistent, there should be only one "id" that points to
>> a
>>> primary key of another table. For example:
>>>
>>> forumCategory
>>> fc_id
>>> fc_name
>>>
>>> forum
>>> forum_id
>>> fc_id
>>> forum_name
>>>
>>> You can use whatever naming standards you want (like I used both a
>> fc_ and a
>>> <tablename>_ type prefix) - that doesn't matter, so long as it's
>> clear - but
>>> the much bigger issue is that if you call forumCategory.fc_id - it
>> needs to
>>> be called fc_id everywhere else too.
>>>
>>> HTH
>>>
>>>
>>> "Bryan Martin" <spam@.myplaceinspace.com> wrote in message
>>> news:9yx3e.13042$JL2.385089@.twister.southeast.rr.c om...
>>> > Given the following table setup
>>>>> > Table
>>> > column1
>>> > column2
>>>>> > forumCategory
>>> > ID
>>> > name
>>>>> > forum
>>> > ID
>>> > name
>>> > forumCategoryID
>>>>> > ForumTopics
>>> > ID
>>> > forumID
>>>>> > ForumTopicMsg
>>> > ID
>>> > topicID
>>> > dateEntered
>>>>> > I am attempting to create a query that gives a complete overview
>> which
>>> > will show all the forumCategory's, followed by all its forums while
>> giving
>>> > a count of all topics belonging to that forum, counting all msgs
>> belonging
>>> > to that topic, and the date of the last msg belonging to that
>> topic.
>>>>> > forumCategory.name
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forumCategory.name
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forumCategory.name
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>> > forum.name, Count(forumTopics.ID), Count(forumTopicMsg.ID),
>>> > Newest(forumTopicMsg.dateEntered)
>>>>> > I cannot figure out how to maintain a relationship across all table
>> and
>>> > still returning all info. This is further compounded by the fact
>> that I
>>> > need to also return the forum.name even if no topic(s) yet exist
>> for that
>>> > forum. So my attempts at using a group by clause have failed. I
>> would
>>> > appreciate any help or insight anyone could offer.
>>>>>>