mysql - 为什么我的查询返回 "Empty set (0.00 sec)"？

Question

已解决-运行更新建筑 SETbuilding_name = "Main Street Building"WHEREbuilding_id = 2;从那里，任务 3 查询将返回正确的结果。

任务一:

查询:

SELECT first_name, last_name, building_name, room.room_id, meeting_start,meeting_end
FROM meeting, person, person_meeting,room, building
WHERE room.room_id=meeting.room_id
AND meeting.meeting_id=person_meeting.meeting_id
AND person.person_id=person_meeting.person_id
AND room.building_id=building.building_id
AND person.first_name='Tom'
AND person.last_name='Hanks';

任务二:

查询:

SELECT first_name, last_name, building_name, room.room_id, meeting_start,meeting_end
FROM meeting, person, person_meeting,room, building
WHERE room.room_id=meeting.room_id
AND meeting.meeting_id=person_meeting.meeting_id
AND person.person_id=person_meeting.person_id
AND room.building_id=building.building_id
AND meeting.meeting_id=2;

任务三:

查询:

SELECT first_name, last_name, building_name, room.room_id,meeting.meeting_id, meeting_start, meeting_end
FROM meeting, person, person_meeting,room, building
WHERE room.room_id=meeting.room_id
AND meeting.meeting_id=person_meeting.meeting_id
AND person.person_id=person_meeting.person_id
AND room.building_id=building.building_id
AND building_name='Main Street Building';

任务四:

查询:

SELECT count(person_id) 'Count of meeting attendees', meeting.meeting_id,meeting_start,meeting_end
FROM meeting, person_meeting
WHERE meeting.meeting_id=person_meeting.meeting_id
GROUP BY meeting.meeting_id;

任务五:

查询:

SELECT first_name, last_name, meeting.meeting_id, meeting_start, meeting_end
FROM meeting INNER JOIN person_meeting ON meeting.meeting_id=person_meeting.meeting_id
INNER JOIN person ON person.person_id=person_meeting.person_id
AND meeting_start<'2016-12-25 12.00.00';

任务 1、2、4、5 和 5 都完美运行。如果你们都需要任务提示，请告诉我。

Answer 1

注意:我还没有对此进行测试。

我根据我们最近的互动更改了查询。

这是您的原创

SELECT first_name, last_name, building_name, room.room_id,meeting.meeting_id, meeting_start, meeting_end    
FROM meeting, person, person_meeting,room, building    
WHERE room.room_id=meeting.room_id    
    AND meeting.meeting_id=person_meeting.meeting_id    
    AND person.person_id=person_meeting.person_id    
    AND room.building_id=building.building_id    
    AND building_name='Main Street Building';

这就是我尝试构建它的方式

根据文档中的第 2 页和第 3 页，您提供了 https://snhu.brightspace.com/d2l/lor/viewer/viewFile.d2lfile/76437/9523,2/ ，我会执行以下操作:

首先，我会获取有关建筑物的信息。

SELECT building_id
FROM building
WHERE building_name like  '%Main Street Building%';

注意:我假设建筑物名称是正确的。但是，为了以防万一，我在名称前后使用通配符 %

接下来，我将获取有关房间的信息:

SELECT b.building_name, r.room_id
FROM room r
  LEFT JOIN building b
    ON r.building_id = b.building_id
WHERE b.building_name like  '%Main Street Building%';

接下来，我会获取有关 session 的信息:

SELECT b.building_name, r.room_id, m.meeting_id, m.meeting_start, m.meeting_end
FROM room r
  LEFT JOIN building b
    ON r.building_id = b.building_id
  LEFT JOIN meeting m
    ON r.room_id = m.room_id
WHERE b.building_name like  '%Main Street Building%';

最后，我会获取参加该 session 的人员的 ID 信息并显示他们的姓名:

SELECT b.building_name, r.room_id, m.meeting_id, m.meeting_start, m.meeting_end, p.first_name, p.last_name
FROM room r
  LEFT JOIN building b
    ON r.building_id = b.building_id
  LEFT JOIN meeting m
    ON r.room_id = m.room_id
  LEFT JOIN persom_meeting pm
    ON m.meeting_id = pm.meeting_id
  LEFT JOIN person p
    ON pm.person_id = p.person_id
WHERE b.building_name like  '%Main Street Building%';

如果万一这些查询不起作用，我建议您确保表之间存在关系。

这意味着表之间应该存在building_id 匹配、room_id 匹配、meeting_id 匹配和person_id 匹配。另外，我会检查建筑物名称的拼写是否正确，因为它区分大小写。

我使用 LEFT JOIN 的原因是这样我可以显示以前的表(所有行)的所有信息以及 ID 与外键 ID 匹配的记录。正如这里所解释的:http://www.acarlstein.com/?p=4168

另外，在 http://www.acarlstein.com/?p=4194 ，我正在展示两个左(外)连接如何工作。

但是，就您而言，它更像是正在构建的一条链，因此它与图表不匹配，但您也许可以明白我的意思。