0

I have 3 tables:

  • Classrooms
  • Courses
  • Members

Screenshot of tables

This is the members table

-----------------------------------------
|  Member Name  |     Preferences       |
-----------------------------------------  
|    Jhon Doe   |    CS201,CS304,CS604  |
|    Neo        |    CS201,CS504,CS302  |
|    Jhon       |    CS201,CS701,CS704  |
-----------------------------------------

Rules for preferences :

  • If there are more than one faculty member willing to take a course then preferred faculty member for that courses will be the one with higher designation (e.g. CS201 and CS304 in the above ‘Preference List’ table).

My question :

How can I get the preferences of the members from the given members who are willing to take the one course among their`s preferences courses

------------------------------------------------------
|  Course-Code  |     Willing Members   |  Preferred |
------------------------------------------------------  
|    CS201      |     Jhon Doe,Neo,Jhon |       Neo  |
|    CS304      |     Jhon Doe,Neo,Jhon |       Jhon |
------------------------------------------------------
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SJ SH
  • 11
  • 2
  • 1
    For the Willing Members column, you can use GROUP_CONCAT. check this https://stackoverflow.com/questions/19558443/comma-separated-string-of-selected-values-in-mysql – jelliaes Jan 12 '18 at 08:08
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Jan 12 '18 at 08:35
  • @jelliaes it doesn`t work for me i did but no luck – SJ SH Jan 12 '18 at 08:58

1 Answers1

0

You have to do a join between the courses and the members table on the common attribute; course_id. The result is to be grouped on the course_id and prefer fields so as to obtain the willing members using group_concat function. Here is the query:

SELECT A.course_id `Course-Code`, GROUP_CONCAT(DISTINCT B.name ORDER BY B.name SEPARATOR ',') 
`Willing Members`, B.prefer `Preferred` FROM courses A
LEFT JOIN members B ON A.course_id=B.course_id
GROUP BY A.course_id, B.prefer;
cdaiga
  • 4,861
  • 3
  • 22
  • 42
  • i Group_CONCAT that worked ,but how can i get the prefer member among the willing members on the basis of above preferences rule ? – SJ SH Jan 13 '18 at 05:57