Joining multiple tables in MySQL

Question

I have the following table structure:

`user`
id(PK)
name

`course`
id(PK)
classid(FK) --> class.id
teacherid(FK)  --> user.id
starttimeid(FK) -->  timetable_time.id
endtimeid(FK) -->  timetable_time.id
name

`class`
id(PK)
name

`course_timetableday`
id(PK)
timetable_dayid(FK) --> timetable_day.id

`timetable_day`
id(PK)
value

`timetable_time`
id(PK)
value

I want to show all the courses for a particular teacherid along with its classname, timetable_day.value and timetable_time.value(starttimeid and endtimetimeid).

I have tried the following query:

SELECT `course`.*, `class`.`name`, `timetable_day`.*, `course_timetable`.*, `timetable_time`.* FROM (`course`) JOIN `class` ON `class`.`id` = `course`.`classid` JOIN `user` ON `user`.`id` = `course`.`teacherid` 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`.`starttimeid` JOIN `timetable_time` AS tt2 ON `tt2`.`id` = `course`.`endtimeid` WHERE `user`.`id` = 0

This gives me the following error(Though, the table is present. I've tried other queries from the same table and they all work):

Unknown table 'timetable_time'

Depending on what you are trying to get out of this, you can eliminate duplicates by utilizing `INNER JOIN` (instead of `JOIN`). Additionally, you can utilize [GROUP BY](http://stackoverflow.com/questions/3091349/using-select-distinct-in-mysql) or `DISTINCT` — random_user_name, Dec 22 '13 at 19:02
Um, unless there's something specific about which I'm unaware of, `JOIN` is just a shortened form of `INNER JOIN`; they're functionally identical. @con_28 - What's up with The separated date/time values? Why not just put a timestamp for the start/end timestamps (end one would be exclusive)? — Clockwork-Muse, Dec 23 '13 at 12:03

score 2 · Accepted Answer · answered Dec 21 '13 at 18:07

Because you have aliased the table `timetable_time as tt1 and again as tt2, your select statement needs to be modified to use tt1 or tt2, like so:

SELECT `course`.*, ... , tt1.* FROM ...

Like so:

SELECT `course`.*, `class`.`name`, `timetable_day`.*, `course_timetable`.*, tt1.* 
    FROM (`course`) 
        JOIN `class` 
            ON `class`.`id` = `course`.`classid` 
        JOIN `user` 
            ON `user`.`id` = `course`.`teacherid` 
        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`.`starttimeid` 
        JOIN `timetable_time` AS tt2 
            ON `tt2`.`id` = `course`.`endtimeid` 
        WHERE `user`.`id` = $i

NOTE:

You don't need to surround table and field names in the tick marks unless they are reserved words. So, your query could read: SELECT course.*, ...

The above query is giving duplicate results. How could I eliminate them. — xan, Dec 21 '13 at 19:04

score 0 · Answer 2 · answered Dec 23 '13 at 11:45

Your table structure does not match you query, there is a table 'course_timetableday' in the structure, but in your query there is a table called 'course_timetable', which apparently has a column called 'courseid'. I assumed the table is named 'course_timetableday'. Here is the query, see remarks below:

SELECT course.*, class.`name` AS classname, timetable_day.value, tt1.value AS starttime, tt2.value AS endtime 
FROM course
JOIN class ON class.id = course.classid
JOIN user ON user.id = course.teacherid
JOIN course_timetableday ON course_timetableday.courseid = course.id
JOIN timetable_day ON timetable_day.id = course_timetableday.timetable_dayid
JOIN timetable_time AS tt1 ON tt1.id = course.starttimeid
JOIN timetable_time AS tt2 ON tt2.id = course.endtimeid
WHERE user.id = 0;

I put an alias on class.name, otherwise it might overwrite course.name (depending on your client program/adapter). I also put an alias on tt1.value and tt2.value for the same reason. If you need the IDs from the joined tables in the result, you might need to alias them also.

If you get duplicate rows in the result set, check if there is really only one entry in the course_timetableday and class tables for every course. Otherwise you get one result row for every matching entry in those tables.

Joining multiple tables in MySQL

2 Answers2