sql - group_concat vs another query -

i ask better solution application. have 1 table: marks - store marks - name, symbol , description. have table: products - each product has marks 1 - n. store marks of products in table products - column prod_marks - comma separated. getting marks query. ids of marks products table , run query them. or 1 query join , group_concat table lets say: prodmarks (pmark_id, pmark_prodid, pmark_markid) better?

thanks

edit:

table ci_products

table ci_marks (all possible marks symbol , description)

now query looks this:

select `prod_id`, `prod_name`, `prod_desc`, `prod_num`, `prod_marks`, `prod_minprice`, `prod_minbid`, `prod_cat`, group_concat(img_url) images (`ci_products`) join `ci_prodimages` on `ci_prodimages`.`img_pid`=`ci_products`.`prod_id` `prod_cat` =  '4' or `prod_cat` =  '8' or `prod_cat` =  '9' or `prod_cat` =  '10' group `prod_id` 

result of query:

then in prod_marks this: 2,5,6 , ids run query marks' name , description.

the 2nd idea make join new table: ci_prodmarks - there me pmark_id, pmark_pid (fk prod_id), pmark_mid (fk mark_id) , group_concat on mark symbol , mark description

edit:

this now. these data run query fetch marks. question is: should make table realated ci_products , ci_marks make m-n relation ? want have query fetch products marks related them. when use join result multiple products each image , mark used group_concat. use group_concat mark_mark , mark_desc ?

edit:

new sql relation table multiple result problem

    select  `prod_id` ,  `prod_name` ,  `prod_desc` ,  `prod_num` ,  `prod_marks` ,  `prod_minprice` ,  `prod_minbid` ,  `prod_cat` , group_concat( img_url ) images, group_concat( mark_mark ) marks, group_concat( mark_desc separator  ";" ) mark_descriptions ( `ci_products` ,  `ci_marks` ) join  `ci_prodimages` on  `ci_prodimages`.`img_pid` =  `ci_products`.`prod_id`  join  `ci_prodmarks` on  `ci_prodmarks`.`pmark_pid` =  `ci_products`.`prod_id`  , ci_prodmarks.pmark_mid = ci_marks.mark_id  `prod_cat` =  '4' or  `prod_cat` =  '8' or  `prod_cat` =  '9' or  `prod_cat` =  '10' group  `prod_id` ,  `mark_id`  

one of problems grouping mark_id, mean end multiple rows per product, , not marks in each row, hiding problem. because have multiple 1 many relationships getting cartesian products in join. imagine following simple example prod_id = 1

images

pid     url -------------- 1       some_url 1       some_other_url 

marks

pid     mark ------------------- 1       1 1       2 

when join these 2 on product id end with

pid     url             mark ------------------------------ 1       some_url        1 1       some_url        2 1       some_other_url  1 1       some_other_url  2 

so combinations of two, when group_concat on these duplicates.

you either need use subqueries group concats:

select  p.prod_id,           p.prod_name,           p.prod_desc,           p.prod_num,           p.prod_marks,           p.prod_minprice,          p.prod_minbid,          p.prod_cat,          img.images,          m.marks,         m.mark_descriptions    ci_products p         inner join          (   select  i.img_pid, group_concat( i.img_url ) images                ci_prodimages             group i.img_pid         ) img             on img.img_pid = p.prod_id          inner join          (   select  ci_prodmarks.pmark_pid,                      group_concat( ci_marks.mark_mark ) marks,                      group_concat( ci_marks.mark_desc separator  ";" ) mark_descriptions                ci_prodmarks                      inner join ci_marks                         on ci_prodmarks.pmark_mid = ci_marks.mark_id             group ci_prodmarks.pmark_pid         ) m             on m.pmark_pid = p.prod_id    p.prod_cat in ('4', '8', '9', '10') 

example on sql fiddle

or use distinct in group_concat:

select  p.prod_id,           p.prod_name,           p.prod_desc,           p.prod_num,           p.prod_marks,           p.prod_minprice,          p.prod_minbid,          p.prod_cat,          group_concat( i.img_url ) images,          group_concat( distinct m.mark_mark ) marks,          group_concat( distinct m.mark_desc separator  ";" ) mark_descriptions    ci_products p         inner join ci_prodimages             on i.img_pid = p.prod_id          inner join ci_prodmarks pm             on pm.pmark_pid = p.prod_id          inner join ci_marks m             on pm.pmark_mid = m.mark_id   p.prod_cat in ('4', '8', '9', '10') group p.prod_id, p.prod_name, p.prod_desc, p.prod_num, p.prod_marks, p.prod_minprice, p.prod_minbid, p.prod_cat 

example on sql fiddle

n.b.

i've replaced this:

where `prod_cat` =  '4' or `prod_cat` =  '8' or `prod_cat` =  '9' or `prod_cat` =  '10' 

with single in statement:

where   p.prod_cat in ('4', '8', '9', '10')