CODEWITHSUNDEEP
mysqlinner-join
miiike test
miiike test

Reputation: 103

how to display multiple values, separated by commas, at single columns?

I am using mysql to perform queries. have the following 7 tables.

I have tried 'concat', 'find_in_set' & 'in' functions but cannot get it to work. How may I display multiple values @ relations 'appointment' & 'module'?

The following statement is the closest i can get. Please let me know if additional details are required, thank you.

SELECT sta.staName
      , r.rank
      , sta.appointmentID
      , a.appointment
      , m.moduleCode 
  FROM staff AS sta 
 JOIN rank AS r 
    ON (sta.rankID = r.rankID) 
 JOIN appointment AS a 
ON (sta.appointmentID = a.appointmentID) 
 JOIN module AS m 
    ON (sta.teachModuleID = m.moduleID) 
  WHERE sta.genderID = 1;

Upvotes: 0

Views: 106

Answers (1)

juergen d
juergen d

Reputation: 204904

SELECT sta.staName, r.rank, sta.appointmentID, 
       group_concat(distinct a.appointment) as appointments,
       group_concat(distinct m.moduleCode) as moduleCodes
FROM staff AS sta 
INNER JOIN rank AS r ON sta.rankID = r.rankID
INNER JOIN appointment AS a ON find_in_set(sta.appointmentID, a.appointmentID) > 0
INNER JOIN module AS m ON find_in_set(sta.teachModuleID, m.moduleID) > 0
WHERE sta.genderID = 1
GROUP BY sta.staName, r.rank, sta.appointmentID

Upvotes: 1

Related Questions