使用多个JOIN语句的SQL查询 [英] SQL Query using multiple JOIN statements
问题描述
我有下表:
tblEvents
(ID,eventTitle,eventDate,地点)
tblVenues
(ID,会场名称)
tblDJs
(ID,名称)
tblEventDJs
(djID,eventID)
tblVenueDJs
(djID,场地ID,日期播放)
这些表具有以下关系:
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
现在,我需要检索dj播放的位置和时间.例如:
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
我已经尝试了以下sql查询,但到目前为止没有用.请记住,我正在使用Microsoft的Acess.我当时在想,也许我应该使用LEFT JOIN而不是INNER JOIN.欢迎您提供任何帮助.
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)
推荐答案
如果我理解这种关系在数据库中正确...下面的查询获取所有DJ及其事件.
If i understand the relations in database correctly ... the following query fetch all DJ''s and them events.
SELECT tblDJs.ID, tblDJs.name, tblEvents.ID, tblEvents.title, tblEvents.eventDate, tblVenues.ID, tblVenues.name, tblVenueDJs.datePlaying
FROM tblDJs LEFT JOIN tblEventDJs ON tblDJs.ID = tblEventDJs.djID
LEFT JOIN tblVenueDJs ON tblDJs.ID = tblVenueDJs.djID
LEFT JOIN tblEvents ON tblEvents.ID = tblEventDJs.eventID
LEFT JOIN tblEvents ON tblEvents.ID = tblVenueDJs.eventID
LEFT JOIN tblVenues ON tblVenues.ID = tblEvents.venue
是的,通过示例结果,您应该使用内部,左侧和右侧联接这样的内容
yes, by the example results you want you should use inner, left and right joins something like this
SELECT tblDJs.ID,
tblDJs.name,
tblEvents.ID,
tblEvents.title,
tblEvents.eventDate,
tblVenues.ID,
tblVenues.name,
tblVenueDJs.datePlaying
FROM tblEvents
INNER JOIN ((tblVenues
LEFT OUTER JOIN tblVenueDJs
ON tblVenues.ID = tblVenueDJs.venueID)
INNER JOIN (tblDJs
LEFT OUTER JOIN tblEventDJs
ON tblDJs.ID = tblEventDJs.djID)
ON tblVenueDJs.djID = tblDJs.ID)
ON (tblEvents.venue = tblVenues.ID)
AND (tblEvents.ID = tblEventDJs.eventID)
这篇关于使用多个JOIN语句的SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!