S
Spektre
This is actually a MS Access/SQL, but used in conjunction with VB.net
This is a "simple" multitable join question. Some skeleton data to work
with is provided. Consider a database of collected classic television
episodes.
Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX
Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5
Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2
Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo
In text, the tables define respectively, TV networks, Series Names, Episode
Names, and finally which episodes a person has collected. Going down the
list each table entry has a 1 to many mapping (ie many series per network,
many episodes per series, many possible collected episodes (via different
media) per episode.)
I would like a query that shows, for the entire collection (entries in
CollectedEpisodes), how many of the episodes are from each series...from each
network.
This for the skeleton data given above. The query would return.
Roseanne 2
Cheers 2
House 0
and
ABC 2
NBC 2
How would I accomplish that? Simple joins I understand...this one is beyond
me.
I would expect the answer to fall into the category of "here is the Join
syntax to return all records from 'NBC'" and then do a count, but I cannot
work out the syntax.
Also secondly, is one query possible that says
"For each series that has at least one Collected Episode, here are the
Episodes needed to complete the respective series."
Thanks in advance
This is a "simple" multitable join question. Some skeleton data to work
with is provided. Consider a database of collected classic television
episodes.
Table: Networks
NetworkID Network
1 NBC
2 CBS
3 ABC
4 PBS
5 FOX
Table: Series
SeriesID SeriesName NetworkID
1 Roseanne 3
2 Cheers 1
3 House 5
Table: Episodes
EpisodeID EpisodeName SeriesID
1 A New Becky 1
2 Roseanna Sings 1
3 Norm's Beer 2
4 Sammy Goes Long 2
Table: CollectedEpisodes
CollectedEpisodeID EpisodeID Format
1 1 VHS
2 1 DVD
3 3 TiVo
4 4 TiVo
In text, the tables define respectively, TV networks, Series Names, Episode
Names, and finally which episodes a person has collected. Going down the
list each table entry has a 1 to many mapping (ie many series per network,
many episodes per series, many possible collected episodes (via different
media) per episode.)
I would like a query that shows, for the entire collection (entries in
CollectedEpisodes), how many of the episodes are from each series...from each
network.
This for the skeleton data given above. The query would return.
Roseanne 2
Cheers 2
House 0
and
ABC 2
NBC 2
How would I accomplish that? Simple joins I understand...this one is beyond
me.
I would expect the answer to fall into the category of "here is the Join
syntax to return all records from 'NBC'" and then do a count, but I cannot
work out the syntax.
Also secondly, is one query possible that says
"For each series that has at least one Collected Episode, here are the
Episodes needed to complete the respective series."
Thanks in advance