I have the following tables:
tblEvents
(ID, eventTitle, eventDate, venue)
tblVenues
(ID, venueName)
tblDJs
(ID, Name)
tblEventDJs
(djID, eventID)
tblVenueDJs
(djID, venueID, datePlaying)
These tables have the following relationships:
<br />
tblEvents.venue = tblVenues.ID<br />
tblEventDJs.djID = tblDJs.ID<br />
tblEventDJs.eventID = tblEvents.ID<br />
tblVenueDJs.djID = tblDJs.ID<br />
tblVenueDJs.eventID = tblEvents.ID
Now, I need to retrieve where and when a dj will be playing. For example:
Column Name Sample #1 Sample #2
tblDJs.ID 1 1
tblDJs.name DJ A DJ A
tblEvents.ID 1 <empty>
tblEvents.title Party A <empty>
tblEvents.eventDate 12/5/2012 <empty>
tblVenues.ID 1 2
tblVenues.name Club A Club B
tblVenueDJs.datePlaying <empty> 13/5/2012
I have tried the following sql query but so far to no avail. Keep in mind that I'm making use of Microsoft's Acesss. I was thinking that maybe I should be using a LEFT JOIN instead of INNER JOIN. Any help is more then welcome.
SELECT tblDJs.ID,
tblDJs.name,
tblEvents.ID,
tblEvents.title,
tblEvents.eventDate,
tblVenues.ID,
tblVenues.name,
tblVenueDJs.datePlaying
FROM tblEvents
INNER JOIN ((tblVenues
INNER JOIN tblVenueDJs
ON tblVenues.ID = tblVenueDJs.venueID)
INNER JOIN (tblDJs
INNER JOIN tblEventDJs
ON tblDJs.ID = tblEventDJs.djID)
ON tblVenueDJs.djID = tblDJs.ID)
ON (tblEvents.venue = tblVenues.ID)
AND (tblEvents.ID = tblEventDJs.eventID)