Output relational table data

I'm trying to output some data from a relational table I set up in MySQL and having problems.

I have 3 MySQL tables: courses, faculty and courses_faculty. I set it up this way to handle the fact that any course can have multiple instructors (faculty). I've been able to insert, update, and display individual records (of a single course) but I'm now having problems trying to output a table that shows each course in a single row with multiple instructors.

Here's the relevant database table structures:

courses:
- id
- facultyID
- courseNumber, courseTitle, etc.

faculty:
- id
- firstName
- lastName
- facultyEmail, etc.

courses_faculty:
- id
- coursesID
- facultyID

What I'm trying to create is a table that looks like this:

Course # - Course Title - Instructor(s)
  300       Top Soil      Joe Smith, Tom Hardy

Open in new window


The Instructor(s) column would pull in the firstName and lastName from the faculty db table.

I've tried creating a recordset with series of JOINs, but the closest I can get repeats a course listing for each instructor, like this:

Course # - Course Title - Instructor(s)
  300      Top Soil       Joe Smith
  300      Top Soil       Tom Hardy

Open in new window


Any ideas on how to do this?

Thanks - Joe
JoeLoweryAsked:
Who is Participating?
 
JacobfwConnect With a Mentor Commented:
Try this with the group by

SELECT courses.id, courses.courseNumber, courses.courseTitle,
courses.courseCrossListed, courses.fall2011,
courses.spring2012, courses.fall2012,
courses.spring2013, courses.fall2013,
courses.spring2014,
GROUP_CONCAT(CONCAT(faculty.firstName,' ',faculty.lastName))
AS theInstructors
FROM courses
INNER JOIN faculty_course ON courses.id = faculty_course.courseID
INNER JOIN faculty
ON faculty_course.facultyID = faculty.id WHERE courseNumber <= '500'  
Group by  courses.id
0
 
JacobfwCommented:
In MYSQL, use the GROUP_CONCAT
0
 
JacobfwCommented:
Here is the documentation.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Let me know if you need assistance with it.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
JoeLoweryAuthor Commented:
That looks like that might just be the ticket. However, I'm having problems trying to nest a CONCAT function in GROUP_CONCAT. Is that not possible?

Here's what I have that's generating a 1064 error:

SELECT courses.id, courses.courseNumber, courses.courseTitle, courses.courseCrossListed, courses.fall2011, courses.spring2012, courses.fall2012, courses.spring2013, courses.fall2013, courses.spring2014, GROUP_CONCAT(CONCAT(faculty.firstName,' ', faculty.lastName) AS 'theName'), faculty.id, faculty_course.facultyID, faculty_course.courseID, faculty_course.ID FROM courses INNER JOIN faculty_course ON courses.id = faculty_course.courseID INNER JOIN faculty ON faculty_course.facultyID = faculty.id WHERE courseNumber <= '500'

Open in new window


What am I doing wrong?

Thanks - Joe
0
 
JacobfwCommented:
Try moving the AS 'the name' outside the group_concat
0
 
JacobfwCommented:
And I think you must get ride of the columns returned from the facilty table (after the group_concat) because they would be unique for each instructor?
0
 
JoeLoweryAuthor Commented:
Ok, I figured out that problem - I needed to move the "AS" clause outside of the GROUP_CONCAT. But now I've got another issue. Now, I'm getting all the instructors for all the courses returned by the recordset in a single line. In other words, if my database has these two records:

Course # - Course Title - Instructor(s)
  300      Top Soil       Joe Smith, Tom Hardy
  310      Bottom Soil    Frank Rocker, Jim Cobble

Open in new window


my output is now:

Course # - Course Title - Instructor(s)
  300      Top Soil       Joe Smith, Tom Hardy, Frank Rocker, Jim Cobble

Open in new window


Here's my current Select statement:

SELECT courses.id, courses.courseNumber, courses.courseTitle, courses.courseCrossListed, courses.fall2011, courses.spring2012, courses.fall2012, courses.spring2013, courses.fall2013, courses.spring2014, GROUP_CONCAT(CONCAT(faculty.firstName,' ',faculty.lastName)) AS theInstructors, faculty.id, faculty_course.facultyID, faculty_course.courseID, faculty_course.ID FROM courses INNER JOIN faculty_course ON courses.id = faculty_course.courseID INNER JOIN faculty ON faculty_course.facultyID = faculty.id WHERE courseNumber <= '500'

Open in new window


How can I adjust it to get multiple records with multiple instructors?

Thanks - Joe
0
 
JoeLoweryAuthor Commented:
That's perfect! Thanks so much Jacob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.