Multiple table join with summation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple table join with summation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-27 : 09:02:52

Aaron writes "Windows 2000 Pro
SQL Server 2000 SP3

I've though about this result-set I need and I'm wondering if there is a way to get the result in one query. Here's my table layout:


Episodes
EpisodeID   EpisodeName    EpisodeNumber
1           E01            1
2           E02            2

Words
WordID   Word
1        am
2        is
3        are
4        were

EpisodesRecycledWords
ERWID    EpisodeID    WordID    Frequency
1        1            1         9
2        2            2         10
3        2            4         6

For every word in the Words table, I want to know which episodes the word was recycled in and how many times per episode. One row of the result-set might look like:


Word: AM
Episodes      Frequency
Episode 1     9
Episode 2     10
Total         19

I know I could query the Words table and then loop over the Words result-set and for each word query the EpisodeRecycledWords table to get what I want. But this solution would produce 2000 queries for 1000 words. I'd love to hear what you guys think!! This one is challenging for me."

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-27 : 09:36:51

Is this a school project?

CFGURU
Starting Member

6 Posts

Posted - 2004-04-27 : 14:01:10

quote:
Originally posted by RickD

Is this a school project?

Not sure why that would matter, but no, it's not for school. I've been out of school for years.

| www.trajiklyhip.com
| Certified Macromedia FlashMX Developer
| Team Macromedia for ColdFusion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 14:03:25

It matters because we don't answer homework questions as the person will not learn anything if we do their homework for them.

If you want an answer to your question, please post your table and data in the form of CREATE TABLE and INSERT INTO. This allows us to copy the information over to our machines and come up with a solution.

Tara

CFGURU
Starting Member

6 Posts

Posted - 2004-04-27 : 14:10:28

quote:
Originally posted by tduggan

It matters because we don't answer homework questions as the person will not learn anything if do their homework for them.

If you want an answer to your question, please post your table and data in the form of CREATE TABLE and INSERT INTO. This allows us to copy the information over to our machines and come up with a solution.

Tara

| www.trajiklyhip.com
| Certified Macromedia FlashMX Developer
| Team Macromedia for ColdFusion

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-27 : 14:14:39

The reason we sometimes ask about school projects is that we occasionally get lazy people coming here asking us to do their homework for them, and we have a policy against answering homework questions.

Your task looks like it could be done with a JOIN and a SUM/GROUP BY, in general. But the display you have doesn't really support that. The display you show could be handled by the client app, though.

But aren't your sample results inconsistent with your data? It looks to me like the only place the word AM (Word ID = 1) shows up is in Episode 1. I don't see it in Episode 2.

I'm also concerned about the Episode Number and EpisodeID columns. In your example they are the same. Will they always be? Couldn't the PK just be Episode Number and skip using the EpisodeID column?

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

CFGURU
Starting Member

6 Posts

Posted - 2004-04-27 : 14:15:08

Not sure why it omitted my response from the previous post. I'll try
again.

Thanks for the offer Tara. I was hoping that my original message (which included the table layout and sample data) would be enough to illicit some suggestions/responses.

| www.trajiklyhip.com
| Certified Macromedia FlashMX Developer
| Team Macromedia for ColdFusion

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-27 : 14:15:47

[code]
create table #Episodes
(
EpisodeID int,
EpisodeName varchar(5),
EpisodeNumber int
)

create table #Words
(
WordID int,
Word varchar(5)
)

create table #EpisodesRecycledWords
(
ERWID int,
EpisodeID int,
WordID int,
Frequency int
)

insert into #Episodes values (1,'E01',1)
insert into #Episodes values (2,'E02',2)

insert into #Words values (1,'am')
insert into #Words values (2,'is')
insert into #Words values (3,'are')
insert into #Words values (4,'were')

insert into #EpisodesRecycledWords values (1,1,1,9)
insert into #EpisodesRecycledWords values (2,2,2,10)
insert into #EpisodesRecycledWords values (3,2,4,6)

declare @Word varchar(5)

set @Word = 'am'

select
c.Word,
b.EpisodeName,
a.Frequency
from
#EpisodesRecycledWords a
inner join #Episodes b on b.Episodeid = a.EpisodeId
inner join #Words c on c.WordID = a.WordID
where c.Word = @Word

drop table #Episodes
drop table #Words
drop table #EpisodesRecycledWords
[/code]
Changed as you've convinced me...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-27 : 14:17:57

quote:
Originally posted by CFGURU

I was hoping that my original message (which included the table layout and sample data) would be enough to illicit some suggestions/responses.

Some questions go unanswered if not put in the needed format (check out RickD's last post). If you provide the info in the correct format, then a bunch might work on the problem at the same time which means you could get multiple solutions pretty quickly.

Tara

CFGURU
Starting Member

6 Posts

Posted - 2004-04-27 : 14:22:33

quote:
Originally posted by AjarnMark

The reason we sometimes ask about school projects is that we occasionally get lazy people coming here asking us to do their homework for them, and we have a policy against answering homework questions.

Certainly understandable.

quote:
Originally posted by AjarnMark

Your task looks like it could be done with a JOIN and a SUM/GROUP BY, in general. But the display you have doesn't really support that. The display you show could be handled by the client app, though.

I can filter the data for display client-side of course. My concern is trips to the database as the database could grow to more than 1,000 words. If I can get all the data I need (for display) in one query, that would be best.

quote:
Originally posted by AjarnMark

But aren't your sample results inconsistent with your data? It looks to me like the only place the word AM (Word ID = 1) shows up is in Episode 1. I don't see it in Episode 2.

You're right. Error in my original post on the Web form.

quote:
Originally posted by AjarnMark

I'm also concerned about the Episode Number and EpisodeID columns. In your example they are the same. Will they always be? Couldn't the PK just be Episode Number and skip using the EpisodeID column?

No, they won't always be the same. For instance, I had several episodes in the Episodes table and then deleted some. Naturally, after entering some more the EpisodeID columns don't match the EpisodeNumber column. The EpisodeNumber column is used to track specific episodes and is a number than the client users are concerned with (mainly for display purposes). The ID column is for DB stuff only and never gets displayed.

| www.trajiklyhip.com
| Certified Macromedia FlashMX Developer
| Team Macromedia for ColdFusion

CFGURU
Starting Member

6 Posts

Posted - 2004-04-27 : 15:11:04

Thanks RickD. I'll give it a go.

| www.trajiklyhip.com
| Certified Macromedia FlashMX Developer
| Team Macromedia for ColdFusion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources