help with santax

[Bryan Martin]

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.

[RCS]

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" <sp**@myplaceinspace.com> wrote in message
news:9y********************@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.

[John Bell]

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" <sp**@myplaceinspace.com> wrote in message
news:9y********************@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.

[Bryan Martin]

Thx RCS and John
"John Bell" <jb************@hotmail.com> wrote in message
news:11**********************@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" <sp**@myplaceinspace.com> wrote in message
news:9y********************@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.
>
>

[RCS]

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" <sp**@myplaceinspace.com> wrote in message
news:JB********************@twister.southeast.rr.c om...

Thx RCS and John
"John Bell" <jb************@hotmail.com> wrote in message
news:11**********************@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" <sp**@myplaceinspace.com> wrote in message
news:9y********************@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.
>
>