嘿伙计们,我需要SQL语句的帮助。总共有 5 个表,我需要为 SELECT 语句连接多个表。 这些是表格:
+-----------+------------+------------+
| person_id | first_name | last_name |
+-----------+------------+------------+
| 1 | Tom | Hanks |
| 2 | Anne | Hathaway |
| 3 | Tom | Cruise |
| 4 | Meryl | Streep |
| 5 | Chris | Pratt |
| 6 | Halle | Berry |
| 7 | Robert | De Niro |
| 8 | Julia | Roberts |
| 9 | Denzel | Washington |
| 10 | Melissa | McCarthy |
+-----------+------------+------------+
+-------------+----------------------+
| building_id | building_name |
+-------------+----------------------+
| 1 | Headquarters |
| 2 | Main Street Building |
+-------------+----------------------+
+---------+-------------+-------------+----------+
| room_id | room_number | building_id | capacity |
+---------+-------------+-------------+----------+
| 1 | 100 | 1 | 5 |
| 2 | 200 | 1 | 4 |
| 3 | 300 | 1 | 10 |
| 4 | 10 | 2 | 4 |
| 5 | 20 | 2 | 4 |
+---------+-------------+-------------+----------+
+------------+---------+---------------------+---------------------+
| meeting_id | room_id | meeting_start | meeting_end |
+------------+---------+---------------------+---------------------+
| 1 | 1 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
| 2 | 1 | 2016-12-25 10:00:00 | 2016-12-25 12:00:00 |
| 3 | 1 | 2016-12-25 11:00:00 | 2016-12-25 12:00:00 |
| 4 | 2 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
| 5 | 4 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
| 6 | 5 | 2016-12-25 14:00:00 | 2016-12-25 16:00:00 |
+------------+---------+---------------------+---------------------+
+-----------+------------+
| person_id | meeting_id |
+-----------+------------+
| 1 | 1 |
| 10 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 2 |
| 8 | 2 |
| 9 | 3 |
| 10 | 3 |
| 1 | 4 |
| 2 | 4 |
| 8 | 5 |
| 9 | 5 |
| 1 | 6 |
| 2 | 6 |
| 3 | 6 |
+-----------+------------+
问题: 构造 SQL 语句以查找 Tom Hanks 必须参加的所有会议。显示以下列: 人名 人的姓氏 建筑物名称 房间号 会议开始日期和时间 会议结束日期和时间
我的声明:
SELECT person.first_name, person.last_name, building.building_name,
->
-> room.room_number, meeting.meeting_start, meeting.meeting_end
->
-> FROM person
->
-> JOIN building
->
-> ON person.person_id = building.building_id
->
-> JOIN room
->
-> ON person.person_id = room.room_id
->
-> JOIN meeting
->
-> ON person.person_id = meeting.meeting_id
->
-> WHERE person_id = 1;
结果:
+------------+-----------+---------------+-------------+---------------------+---------------------+
| first_name | last_name | building_name | room_number | meeting_start | meeting_end |
+------------+-----------+---------------+-------------+---------------------+---------------------+
| Tom | Hanks | Headquarters | 100 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
+------------+-----------+---------------+-------------+---------------------+---------------------+
汤姆·汉克斯(Tom Hanks(有多个会议。(确切地说是4(如何显示 4 个会议?不知道在这里做什么。我的陈述仅显示 1。
能够找到解决方案。不需要加入...
SELECT first_name,last_name ,building_name,room_number ,meeting_start,meeting_end
FROM person P, building B,person_meeting PM,meeting M,room R
WHERE
P.person_id=PM.person_id
AND M.room_id=R.room_id
AND B.building_id=R.building_id
AND PM.meeting_id=M.meeting_id
AND P.first_name='Tom'
AND P.last_name='Hanks';
此查询应该为您提供所需的内容(除非有任何拼写错误:P!
SELECT person.first_name,
person.last_name,
building.building_name,
room.room_number,
meeting.meeting_start,
meeting.meeting_end
FROM person
INNER JOIN person_meeting ON person_meeting.person_id = person.person_id
INNER JOIN meeting ON meeting.meeting_id = person_meeting.meeting_id
INNER JOIN room ON room.room_id = meeting.room_id
INNER JOIN building on building.building_id = room.building_id
WHERE person.person_id = 1
使用此查询,您将联接已创建关系上的表。 一个人通过meeting.person_id = person.person_id
通过room.room_id = meeting.room_id
链接到会议室,房间通过building.building_id = room.room_id
链接到建筑物,然后最终过滤这些内容以仅显示person.person_id = 1
您的查询问题是您试图通过person_id将所有内容加入人身上
JOIN building ON person.person_id = building.building_id
+-------------+----------------------+
| building_id | building_name |
+-------------+----------------------+
| 1 | Headquarters |<-
| 2 | Main Street Building |
+-------------+----------------------+
从这个表中我们只会得到 building_id = 1
JOIN room ON person.person_id = room.room_id
+---------+-------------+-------------+----------+
| room_id | room_number | building_id | capacity |
+---------+-------------+-------------+----------+
| 1 | 100 | 1 | 5 | <-
| 2 | 200 | 1 | 4 |
| 3 | 300 | 1 | 10 |
| 4 | 10 | 2 | 4 |
| 5 | 20 | 2 | 4 |
+---------+-------------+-------------+----------+
从这个表中我们只会得到room_id = 1
JOIN meeting ON person.person_id = meeting.meeting_id
+------------+---------+---------------------+---------------------+
| meeting_id | room_id | meeting_start | meeting_end |
+------------+---------+---------------------+---------------------+
| 1 | 1 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 | <-
| 2 | 1 | 2016-12-25 10:00:00 | 2016-12-25 12:00:00 |
| 3 | 1 | 2016-12-25 11:00:00 | 2016-12-25 12:00:00 |
| 4 | 2 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
| 5 | 4 | 2016-12-25 09:00:00 | 2016-12-25 10:00:00 |
| 6 | 5 | 2016-12-25 14:00:00 | 2016-12-25 16:00:00 |
+------------+---------+---------------------+---------------------+
从这个表格中我们可以看到我们只会得到meeting_id = 1
主键/外键
- Person.person_id是你的PK,meeting.person_id是你的FK
- room.room_id是你的PK,meeting.room_id是你的FK
- Building.building_id是你的PK,room.building_id是你的FK
SQL 小提琴