StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFetching parent and child nodes along with counts
text
Body
copied!<p>I am using the nested model according to a <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow noreferrer">guide from this article</a>.</p> <blockquote> <p>The nested category table is as follows.</p> </blockquote> <p><img src="https://i.stack.imgur.com/xuTXl.png" alt="nested_category"></p> <blockquote> <p>The product table is as follows.</p> </blockquote> <p><img src="https://i.stack.imgur.com/CplWz.png" alt="product"></p> <p>I want to get the parent node & the child nodes of a selected level. Eg: If television was selected the query should display. Additionally the count of each products in each category should also be displayed.</p> <pre><code>Electronics Television Tube (2) LCD (1) Plasma (2) </code></pre> <p>I wrote the following query</p> <pre><code>SELECT parent.name, count(product.product_id) FROM nested_category AS node,nested_category AS parent,nested_category AS midpoint LEFT JOIN product ON product.product_id=midpoint.category_id WHERE (node.lft BETWEEN parent.lft AND parent.rgt) AND (node.lft BETWEEN midpoint.lft AND midpoint.rgt) AND midpoint.name='TELEVISIONS' GROUP BY parent.name ORDER BY node.lft </code></pre> <p>The result I get is here</p> <p><img src="https://i.stack.imgur.com/2Isla.png" alt="Results from my query"></p> <p>This gets all the children & parent node properly but the count is wrong. <a href="http://sqlfiddle.com/#!2/d99d3/4" rel="nofollow noreferrer">Check the SQL Fiddle here</a></p>

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