CODEWITHSUNDEEP
phpmysqlsqljoin
xan
xan

Reputation: 4696

Joining 4 MySQL tables

I've the following tables:

`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`

I want to feed the $userid and want to get the array containing the timetable of that user.

The idea that I'm trying to work is:

  1. Get the userid.
  2. Get the courses corresponding to that userid and the fromtimeid and tilltimeid
  3. Join the timetable_time table to get the values of fromtimeid and tilltimeid fetched from #2.
  4. Get the days from course_timetable table by joining course_timetable.courseid with course.id (fetched from #3)
  5. Join timetable_day and get the values of days.

I've tried doing the following but it throws an error:

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

The error displayed is:

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

Where am I going wrong?

Upvotes: 2

Views: 71

Answers (4)

nl-x
nl-x

Reputation: 11832

You are joining timetable_time twice.

First off I wonder if this is necessary. But If so, you need to give at least one of them an alias:

JOIN SomeTimable AS SomeTableAlias ON ...

Also when referencing that specific table you need to use the alias. So use the alias in the SELECT and the ON clauses.

(btw. you may omit the keyword AS when aliassing a table.)

Upvotes: 2

sdespont
sdespont

Reputation: 14025

Assign alias to your timetable_time table in the join like this :

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

Upvotes: 1

Barmar
Barmar

Reputation: 780974

Since you're joining with the same table multiple times, you need to give them aliases to disambiguate them:

JOIN `timetable_time` t1 ON t1.`id` = `course`.`fromtimeid`
JOIN `timetable_time` t2 ON t2.`id` = `course`.`tilltimeid`

Upvotes: 2

eric.itzhak
eric.itzhak

Reputation: 16062

Your problem is that you're joining the table timetable_time twice and use the same name. Change it to something like:

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

Upvotes: 1

Related Questions