Help w/ difficult foreign key query 2

[jwigal]

I have tried a number of different ways to set this query up, but i can't seem to get it to work.

I am working on a sports referee assignment database. This database has three different elements I am working with: games, leagues and referees. Each referee may belong to one or more leagues. Each game is assigned to one league. My tables are set up as follows:

Table Games
GameID (primary key)
LeagueID (lookup to the League table)

Table Leagues
LeagueID (primary key)
NameOfLeague (text description)

Table Referees
RefereeID (primary key)
RefereeName (name of referee)

Table RefereeLeague (foreign key table)
RefRLID (lookup to primary key in Referee table)
LeagueRLID (lookup to primary key in League table)


The RefereeLeague table is a foreign key table that lists all of the ALLOWED referee-league combinations.

I am trying to generate a query that will generate a list of all games, and each referee that is NOT allowed to work the game. For example:

LEAGUES: (each has a primary key defined)
League A
League B
League C

GAMES:
G#1, League A
G#2, League B
G#3, League C

REFEREES: (each has a primary key defined)
Ref1
Ref2
Ref3
Ref4

REFEREELEAGUE Table (list of allowed referee-league combinations)
Ref1, League A
Ref2, League B
Ref3, League A
Ref3, League B

will generate the following list of conflicts:
G#1, Ref2
G#1, Ref4
G#2, Ref2
G#2, Ref3
G#2, Ref4
G#3, Ref1
G#3, Ref2
G#3, Ref3
G#3, Ref4

any thoughts? this one really has me stumped. thanks in advance

-jeff wigal
jeff@wigaldesign.com

 

[Dodgey]

That looks correct to me, except
the g#2, Ref2 shouldn't be there and g#2, Ref1 should be. Is that a typo?.

To explain why I think you are getting this result set, I introduce a chap named Codd. Can't remember hs first name but he invented 13 rules of Referential Integrity. By looking at your denormalised table model, I imagine you're familiar with a majority of them.

For game 1 all the refs that can't ref that league are apparent.

For game 3, no ref can referee that game as their is no association for any ref and league in the RefereeLeague table.

For game 2, this is where it gets harder. Due to the database design and the referential (or relationship) laws you have created, Access knows...

There is no association for ref 4 to game 2

There is no association for ref 1 to game 2 (I assume here that g#2, Ref2 should read g#2, Ref1), and...

Although Ref3 is qualified for league b a record exists that shows that ref3 is also qualified for league a, in effect making it not available for league b.


Hope that is easy to understand. If so, please vote, If not, try to stay off the refer stuff (joke).

 

[jwigal]

oops, you are right. the results i am looking for are:

G#1, Ref2
G#1, Ref4
G#2, Ref1
G#2, Ref4
G#3, Ref1
G#3, Ref2
G#3, Ref3
G#3, Ref4

For game 1 all the refs that can't ref that league are apparent.

yes

For game 3, no ref can referee that game as their is no association for any ref and league in the RefereeLeague table.

correct, since i am looking to generate a list of referees who CANNOT work the game, it should return all 4 referees for game #3

For game 2, this is where it gets harder. Due to the database design and the referential (or relationship) laws you have created, Access knows...

There is no association for ref 4 to game 2

There is no association for ref 1 to game 2 (I assume here that g#2, Ref2 should read g#2, Ref1), and...

Although Ref3 is qualified for league b a record exists that shows that ref3 is also qualified for league a, in effect making it not available for league b.


exactly... i've tried a ton of ways to do this, shying away from either a make table query or a VBA procedure that will generate a table on demand. am i basically out of luck?

 

[jwigal]

i have not heard of the 13 rules of Referential Integrity. can you elaborate?

 

[Dodgey]

I beg your pardon, there are only twelve rules.

Codd's rules for referental database design are very calculated (he proved you could design a database with calculus, smart guy!) and some of them seem unreasonable (in a business/financial sense). You'd be hard pressed to find any DBMS on the market that strictly adheres to all twelve. a friend told me once that she thought Access only adhered to 10 of them at the most.

I don't have all twelve at hand but the name of the guy is

E. F. Codd

He has written a huge array of bibles addressing database design. You can find him in your local library or on the internet if you've got the time to surf.

 

[Dodgey]

Actually in answer to your question...

YES! it can be done. If you're a great lover of Star Trek, the crew always would ask Spock how to find something in a huge place. Quite logically, he would reply, by finding out where it isn't.

To get an acturate account you need to investigate using sub queries. ie SELECT refs FROM table WHERE NOT IN (SELECT refs FROM table WHERE refs.league = league);

Thats a crude example but I hope you get the jist.

 

[Craig0201]

And for the relational model and rules of referential integrity,

http://www.cis.gsu.edu/~shong/teaching/cis473/slides/rel_dbms.pdf

Craig