I have the table
Articles -------- id,name,type_id
Type--------------id,name
article_type ----------article_id , type_id
I using this query
select A.name from types A
inner join article_type B on ( B.type_id = A.id and article_id = 10)
Now this query is working but it gives me separate rows but i want all types in one variables so that i can display in table format like
Article name ------------types
milk--------------------dairy , persishable , costly
Use MySQL's GROUP_CONCAT function:
SELECT a.name AS article_name,
GROUP_CONCAT(t.name) AS types
FROM ARTICLES a
JOIN ARTICLE_TYPE at ON at.article_id = a.id
JOIN TYPE t ON t.id = at.type_id
GROUP BY a.name