SELECT DISTINCT a.assessement_group_id,
b.title as dataa
FROM assessment_group a
JOIN assessment_category b
WHERE a.assessement_group_id = b.group_id
I am using the join to display the data.the result are shown below
100 Partner Business Profile
99 Partner Activation
99 ajay test
100 ajaytest123
But i want this type of answer
100 Partner Business Profile,ajaytest123
99 Partner Activation,ajay test
You can use GROUP_CONCAT(DISTINCT ...) along with GROUP BY to get the output you want:
SELECT a.assessement_group_id,
GROUP_CONCAT(DISTINCT b.title)
FROM assessment_group a
INNER JOIN assessment_category b
ON a.assessement_group_id = b.group_id
GROUP BY a.assessement_group_id
By the way, I replaced your old-style implicit join syntax with an explicit INNER JOIN. It is generally considered bad practice now to put join conditions into the WHERE clause.
Try something as follows, hope this helps
select id, group_concat(`dataa` separator ',') as `dataa`
from
(
SELECT distinct a.assessement_group_id id, b.title as dataa
from assessment_group a JOIN assessment_category b
WHERE a.assessement_group_id=b.group_id
) tbl
group by id;
See the MySql GROUP_CONCAT() as part of a grouped query.
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
You would add a GROUP_BY assessement_group_id to the end of the query, for example.
In mySql you can use the GROUP_CONCAT function, see here more details: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat.
In your initial query you need to add GROUP_CONCAT(b.title) in select clause and also a group by after assessement_group_id.
This one will help you
SELECT A.ASSESSEMENT_GROUP_ID, GROUP_CONCAT(B.TITLE SEPARATOR ' , ') AS DATAA FROM ASSESSMENT_GROUP A, ASSESSMENT_CATEGORY B WHERE A.ASSESSEMENT_GROUP_ID=B.GROUP_ID GROUP BY A.ASSESSEMENT_GROUP_ID;
