Problem In My Select Query

[thara]

I created this select query to get some rows... Its working. but my problem is I need to remove duplicate rows from the query...

 

this is my query:

 

SELECT tcs.tutor_id, tcs.category_id, tcs.subject_id, s.subjects, t.tutor_name, t.tutor_code
FROM tutor_category_subject as tcs
INNER JOIN subject AS s ON tcs.subject_id = s.subject_id
INNER JOIN tutors AS t ON tcs.tutor_id = t.tutor_id
WHERE s.subjects LIKE '%business%';

 

This is its output:

 

+----------+-------------+------------+-------------------------------+-----------------------+------------+

| tutor_id | category_id | subject_id | subjects | tutor_name | tutor_code |

+----------+-------------+------------+-------------------------------+-----------------------+------------+

| 1 | 6 | 37 | Business Studies | Tharanga Nuwan Kumara | 1250 |

| 3 | 6 | 37 | Business Studies | Kumara | 1252 |

| 15 | 4 | 11 | Business & Accounting Studies | Tharanga Nuwan Kumara | 1264 |

| 15 | 6 | 37 | Business Studies | Tharanga Nuwan Kumara | 1264 |

| 16 | 5 | 11 | Business & Accounting Studies | Kasun Kalhara | 1265 |

| 16 | 6 | 37 | Business Studies | Kasun Kalhara | 1265 |

+----------+-------------+------------+-------------------------------+-----------------------+------------+

 

 

 

Here, you can see tutor id has duplicated in my query. and I need to select all subjects to a tutor in one row separated by comma.

 

eg: (Business & Accounting Studies, Business Studies) like this..

 

So can anybody tell me what I need to do in my select query??

 

Thank you.

Edited October 31, 2012 by thara

[smoseley]

SELECT

tcs.tutor_id,

tcs.category_id,

tcs.subject_id,

GROUP_CONCAT(s.subjects SEPARATOR ',') AS subjects,

t.tutor_name,

t.tutor_code

FROM tutor_category_subject as tcs

INNER JOIN subject AS s ON tcs.subject_id = s.subject_id

INNER JOIN tutors AS t ON tcs.tutor_id = t.tutor_id

WHERE s.subjects LIKE '%business%'

GROUP BY tcs.tutor_id;

[smoseley]

Note: GROUP_CONCAT by default has a limit of 1024 characters. If you expect to exceed that, you should alter your my.cnf to change that.

[thara]

thanks.. Its work