• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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
0
JoeLowery
Asked:
JoeLowery
  • 5
  • 3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
JacobfwCommented:
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
 
JoeLoweryAuthor Commented:
That's perfect! Thanks so much Jacob.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now