Jeremy Leff (jeremyleff) wrote in databases,
Jeremy Leff
jeremyleff
databases

SQL Help

Hey guys (and gals) I need a little SQL help:

I have 4 tables: Players, Books, Events, and Player_Events. The Player_Events table details which Player will play which Book at a particular Event. It is possible to have more than one Player per Book at the same Event, and it is also possible to have no Players on a Book for an event. I need to construct a query which will return "Who is playing what book at this event" but here's the catch.. I  also need it to return a row for any Books for which there is no Player assigned and leave a Null value for Player.

As you can see in the sample data below, there is no one scheduled to play Drums at the Smith Wedding. No record exists to indicate this in the Player_Events table. The only logic that dictates this fact is that there is in fact a Drum Book in Books and no reference to it in Player_Events.

The only solution I can think of so far is to create a function that automatically creates records in Player_Events based on a proposed Default_Book_FK column in Players, but I really don't want to go that route if I can avoid it. Any help is appreciated!

Desired Query:

Who Is Playing The Smith Wedding? Query

Book_FK Player_FK Event_FK
Joe Blow Trumpet The Smith Wedding
John Boy Trumpet The Smith Wedding
Some Guy Sax The Smith Wedding
[Null] Drums The Smith Wedding
... ... ...

Players

Player_ID Player_Name
1 Joe Blow
2 John Boy
3 Some Guy
4 ...

Books

Book_ID Book_Name
1 Trumpet
2 Sax
3 Drums
4 ...

Events

Event_ID Event_Name Date
1 The Smith Wedding 1/1/2000
2 ... ...
3 ... ...

Player_Events

Player_Event_ID Player_FK Book_FK Event_FK
1 1 1 1
2 2 1 1
3 3 2 1

Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 9 comments