asked on

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

Jacobfw

In MYSQL, use the GROUP_CONCAT

Jacobfw

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.

JoeLowery

ASKER

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

Jacobfw

Try moving the AS 'the name' outside the group_concat

Jacobfw

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?

JoeLowery

ASKER

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

ASKER CERTIFIED SOLUTION

Jacobfw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JoeLowery

ASKER

That's perfect! Thanks so much Jacob.