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.Not sure but Try This..
Select C.Name Category, F.Name Forum,
TC.TopicCount, T.ID TopicID,
Count(*) MessageCount,
Max(DateEntered) LastMessageDT
From ForumCategory C
Join Forum F On F.forumCategoryID = C.ID
Join (Select forumID, Count(*) TopicCnt
From ForumTopics Group by ForumID) TC
On TC.ForumID = F.ID
Left Join ForumTopics T On T.forumID = F.ID
Left Join ForumTopicMsg M On M.topicID = T.ID
Group By C.Name, F.Name,TC.TopicCount, T.ID
"Bryan Martin" wrote:

> 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 wil
l
> 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 t
o
> 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.com...
> 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.com...
which
giving
belonging
topic.
and
that I
for that
would|||Thx RCS and John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1112472695.860127.107770@.o13g2000cwo.googlegroups.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:
> topicid =
> a
> fc_ and a
> clear - but
> needs to
> which
> giving
> belonging
> topic.
> and
> that I
> for that
> would
>|||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.com...
> Thx RCS and John
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:1112472695.860127.107770@.o13g2000cwo.googlegroups.com...
>sql