CODEWITHSUNDEEP
mysqlsqljoin
Mythriel
Mythriel

Reputation: 1380

How can i join records from 5 different tables?

I want to retrieve all of the courses with their associated data. For example the categories the course is in and the tutor for the course. Can I do this in one Select statement using joins to retrieve the data I need?

I have to following schema

tbl_courses (id, name, description, tutor_id, date)
tbl_course_categories (id, course_id, category_id) 
tbl_categories (id, name, parent_id)
tbl_users (id, username, email, password)
tbl_tutors (id, first name, last name, user_id)

Upvotes: 0

Views: 79

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21756

SELECT 
  c.NAME AS CourseName,
  ca.Name AS CategoryName,
  t.First_name + t.Last_name AS TutorName
FROM tbl_courses c
JOIN tbl_course_categories cc 
  ON cc.course_id = c.Id
JOIN tbl_categories ca
  ON ca.Id = cc.category_id
JOIN tbl_users u
  ON u.Id = c.tutor_id
JOIN tbl_tutor t 
  ON t.User_id = u.id

or

SELECT 
  c.NAME AS CourseName,
  GROUP_CONCAT(ca.Name+ ' ') AS Categories,
  t.First_name + t.Last_name AS TytorName
FROM tbl_courses c
JOIN tbl_course_categories cc 
  ON cc.course_id = c.Id
JOIN tbl_categories ca
  ON ca.Id = cc.category_id
JOIN tbl_users u
  ON u.Id = c.tutor_id
JOIN tbl_tutor t 
  ON t.User_id = u.id
GROUP BY c.NAME, t.First_name + t.Last_name

Upvotes: 1

Related Questions