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:

Quote
+----------+-------------+------------+-------------------------------+-----------------------+------------+
| 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.

[Ashley_Sheridan]

Try (untested) this:

SELECT tcs.tutor_id, tcs.category_id, tcs.subject_id, GROUP_CONCAT(s.subjects) 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

[thara]

thanks its working..