StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POgroup_concat vs another query
text
Body
copied!<p>I would like to ask what would be better solution for my application. I have 1 table: marks - where I store marks - name, symbol and description. Then I have another table: products - each product has some marks 1 - N. Now I store marks of products in table products - column prod_marks - comma separated. Then I am getting those marks with another query. I get all the IDs of marks from products table and then I run query to get them. Is it good or would 1 query with join and group_concat to another table lets say: prodmarks (pmark_id, pmark_prodID, pmark_markID) be better? </p> <p>Thanks</p> <p>Edit:</p> <p>table ci_products</p> <p><img src="https://i.stack.imgur.com/Bqyu6.png" alt="enter image description here"></p> <p>table ci_marks (all possible marks with symbol and description) <img src="https://i.stack.imgur.com/a1ctO.png" alt="enter image description here"></p> <p>Now my query looks like this:</p> <pre><code>SELECT `prod_id`, `prod_name`, `prod_desc`, `prod_num`, `prod_marks`, `prod_minprice`, `prod_minbid`, `prod_cat`, GROUP_CONCAT(img_url) images FROM (`ci_products`) JOIN `ci_prodimages` ON `ci_prodimages`.`img_pid`=`ci_products`.`prod_id` WHERE `prod_cat` = '4' OR `prod_cat` = '8' OR `prod_cat` = '9' OR `prod_cat` = '10' GROUP BY `prod_id` </code></pre> <p>result of query: <img src="https://i.stack.imgur.com/OlW6p.png" alt="enter image description here"></p> <p>Then I get in prod_marks something like this: 2,5,6 And then with those IDs I run another query to get the marks' name and description.</p> <p>The 2nd idea was to make another join to new table: ci_prodmarks - there would me pmark_id, pmark_pid (fk to prod_id), pmark_mid (fk to mark_id) and also another group_concat on mark symbol and mark description</p> <p><strong>Edit:</strong></p> <p>This is what I get now. With these data I run another query to fetch those marks. The question is: should I make another table realated to ci_products and ci_marks to make M-N relation ? Also then I want to have query to fetch all products with marks related to them. When I use JOIN it would result multiple products for each image and mark so I used group_concat. So then I would use another group_concat for mark_mark and mark_desc ? </p> <p><strong>Edit:</strong></p> <p>New SQL with relation table with multiple result problem</p> <pre><code> 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 FROM ( `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` AND ci_prodmarks.pmark_mid = ci_marks.mark_id WHERE `prod_cat` = '4' OR `prod_cat` = '8' OR `prod_cat` = '9' OR `prod_cat` = '10' GROUP BY `prod_id` , `mark_id` </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload