我有以下表格：
`course`:
`id` (PK)
`userid` (FK)
`fromtimeid` (FK) --> `timetable_time`
`tilltimeid` (FK) --> `timetable_time`
`course_timetable`:
`id` (PK)
`courseid` (FK) --> `course`
`timetable_dayid` (FK) --> `timetable_day`
`timetable_time`:
`id` (PK)
`value`
`timetable_day`:
`id` (PK)
`value`
我想要馈送$userid，并且想要得到包含该用户的时间表的数组。
我正在努力工作的想法是：
- 获取用户ID
- 获取该
userid与fromtimeid、tilltimeid对应的课程 - 加入
timetable_time表以获得从#2获取的fromtimeid和tilltimeid的值 - 通过将
course_timetable.courseid与course.id连接（从#3获取），从course_timetable表中获取天数 - 加入
timetable_day并获取天数值
我尝试过做以下操作，但它抛出了一个错误：
SELECT `course`.*, `course_timetable`.*
FROM (`course`)
JOIN `course_timetable` ON `course_timetable`.`courseid` = `course`.`id`
JOIN `timetable_day` ON `timetable_day`.`id` = `course_timetable`.`timetable_dayid`
JOIN `timetable_time` ON `timetable_time`.`id` = `course`.`fromtimeid`
JOIN `timetable_time` ON `tiemtable_time`.`id` = `course`.`tilltimeid`
WHERE `userid` = $id
显示的错误为：
Error Number: 1066
Not unique table/alias: 'timetable_time'
SELECT `course`.*, `course_timetable`.* FROM (`course`) JOIN `course_timetable` ON `course_timetable`.`courseid` = `course`.`id` JOIN `timetable_day` ON `timetable_day`.`id` = `course_timetable`.`timetable_dayid` JOIN `timetable_time` ON `timetable_time`.`id` = `course`.`fromtimeid` JOIN `timetable_time` ON `timetable_time`.`id` = `course`.`tilltimeid` WHERE `teacherid` = 0
我哪里错了？
由于您多次加入同一个表，因此需要为它们提供别名以消除歧义：
JOIN `timetable_time` t1 ON t1.`id` = `course`.`fromtimeid`
JOIN `timetable_time` t2 ON t2.`id` = `course`.`tilltimeid`
您已加入timetable_time两次。
首先，我想知道这是否有必要。但如果是这样，你需要给其中至少一个别名：
JOIN SomeTimable AS SomeTableAlias ON ...
此外，在引用该特定表时，您需要使用别名。因此，在SELECT和ON子句中使用别名。
（顺便说一句，在对表进行别名处理时，可以省略关键字AS。）
您的问题是两次加入表timetable_time并使用相同的名称。将其更改为类似的内容
SELECT `course`.*, `course_timetable`.*
FROM (`course`)
JOIN `course_timetable` ON `course_timetable`.`courseid` = `course`.`id`
JOIN `timetable_day` ON `timetable_day`.`id` = `course_timetable`.`timetable_dayid`
JOIN `timetable_time` t1 ON t1.`id` = `course`.`fromtimeid`
JOIN `timetable_time` t2 ON t2.`id` = `course`.`tilltimeid` WHERE `userid` = $id
将别名分配给联接中的timetable_time表，如下所示：
SELECT `course`.*, `course_timetable`.*
FROM (`course`)
JOIN `course_timetable` ON `course_timetable`.`courseid` = `course`.`id`
JOIN `timetable_day` ON `timetable_day`.`id` = `course_timetable`.`timetable_dayid`
JOIN `timetable_time` AS `tt1` ON `tt1`.`id` = `course`.`fromtimeid`
JOIN `timetable_time` AS `tt2` ON `tt2`.`id` = `course`.`tilltimeid`
WHERE `userid` = $id