CODEWITHSUNDEEP
mysqlsql
Kuntal Gupta
Kuntal Gupta

Reputation: 11

How to select records with duplicate just one field and all other field value?

i have table name = schedule 

 --------------------------------------------
| course_id | room |   day  |     time     |
--------------------------------------------
|     2     |  401 |saturday| 9:00 - 13:00 |
--------------------------------------------
|     3     |  401 | sunday | 9:00 - 13:00 |
--------------------------------------------
|     2     |  402 | monday | 9:00 - 13:00 |
--------------------------------------------
|     3     |  403 | tuesday| 14:00 - 17:00|
--------------------------------------------
|     4     |  401 | tuesday| 9:00 - 13:00 |
--------------------------------------------
|     2     |  402 |wednesday|14:00 - 17:00|
--------------------------------------------

and another table name = course

----------------------------------
| id | course_code | course_name |
----------------------------------
| 2  |  cse        | cse         | 
------------------------
| 3  |  eee        | eee         | 
----------------------
| 4  |  ct         | ct          | 
-----------------------

Now how to get output like this..

----------------------------------
| course code | course name | info |
----------------------------------
|cse          |  cse        |401,satueday-9:00-13:00; 402,monday-9:00-13:00; 402,wednesday-14:00-17:00|
------------------------
|    eee      | eee         | 401,sunday-9:00-13:00;403,tuesday-14:00-17:00 |
------------------------
|    ct       |  ct         | 401,tuesday-9:00-13:00|
-----------------------

Upvotes: 0

Views: 45

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use group_concat

select c.course_code,
    c.course_name,
    group_concat(concat(s.room, ',', s.day, '-', s.time) separator ';')
from course c
join schedule s on s.course_id = c.id
group by c.course_code,
    c.course_name;

Upvotes: 1

Related Questions