From this forum post by PDM.
<ul>
{exp:query sql="SELECT cat_id as category_id, cat_name AS category_name FROM exp_categories WHERE parent_id = ' **parent category id** '"}
<li><a href="{path={my_template_group}/**your template name **/C{category_id}}">{category_name}</a></li>
{/exp:query}
</ul>
Note that the “C” in the linked URL MUST be uppercase, and you must edit the URL and parent ID to fit your site.
Addendum: Using with embedded templates and passing parent category ID’s to permit Parent categories to display without links. (for example)
Now the parent category displays without a link, while children have correct links to a display page. (See this discussion for why you need to call the query from outside the loop as an embed in order for the href="{path= values to update with the children’s links correctly.)
The requesting template uses the parent_only="yes" parameter to filter out only parent category ID’s, then passes that to the embedded template via a variable, “my_parent”
In template code:
<h2 class="sidetitle">Categories</h2>
{!-- Sift out parent categories for category_id's --}
{exp:weblog:categories weblog="{master_weblog_name}" parent_only="yes"}
{!-- Print Parent Name (optional - leave out if trying to show only children) --}
<h4>{category_name}</h4>
{!-- Send category_id of parent to sql query in embedded template --}
{ embed="base1/_embed_child_query" my_parent="{category_id}" }
{/exp:weblog:categories}
Embedded template (called here “_embed_children_query").
Notice the embedded variable, {embed:my_parent} being assigned to parent_id=
This shows also how SQL queries can handle EE variables.
{!-- keep in mind there are no quotes or ticks around variables used in a SQL statement - ticks only go around absolutes --}
<ul>
{exp:query sql="SELECT cat_id as child_category_id, cat_name AS child_category_name FROM exp_categories WHERE parent_id = {embed:my_parent}"}
<li><a href="{path=science/C{child_category_id}}">{child_category_name}</a></li>
{/exp:query}
</ul>
Category:Categories Category:Queries Category:Embeds Category:Templates