How are parent and child categories related in Craft database? I am writing an API which directly connects to Craft database and retrieves data.  I need to get the categories hierarchy.  But I can not find how parent and child categories are related in the database, therefore I can't write an SQL query to retrieve it. 
Can anyone help with this?
As of Craft 2 all information about Parent - Child relations are stored in the craft_structureelements table. But it's not like a hierarchy (child has the parent id) so it can be quite confusing at the first moment. 
When you take a look into the ElementService you'll see the following how the getChildren() parameter from your ElementCriteriaModel is converted into a Query

if ($criteria-&gt;descendantOf)
{
    $query-&gt;andWhere(
        array('and',
            'structureelements.lft &gt; :descendantOf_lft',
            'structureelements.rgt &lt; :descendantOf_rgt',
            'structureelements.root = :descendantOf_root'
        ),
        array(
            ':descendantOf_lft'  =&gt; $criteria-&gt;descendantOf-&gt;lft,
            ':descendantOf_rgt'  =&gt; $criteria-&gt;descendantOf-&gt;rgt,
            ':descendantOf_root' =&gt; $criteria-&gt;descendantOf-&gt;root
        )
    );

    if ($criteria-&gt;descendantDist)
    {
        $query-&gt;andWhere('structureelements.level &lt;= :descendantOf_level',
            array(':descendantOf_level' =&gt; $criteria-&gt;descendantOf-&gt;level + $criteria-&gt;descendantDist)
        );
    }
}


The ancestorDist is the level of the child. If you only want to have direct children you can ignore that part. $criteria-&gt;descendantOf is actually the BaseElementModel of your current element -> $element-&gt;getChildren . $criteria-&gt;descendantOf-&gt;rgt is the value in craft_structureelements for the certain Id. Imagine element is your current element - your where could be like

WHERE [....] AND (structureelements.lft &lt; element.lft AND structureelements.rgt &gt; element.rgt AND structureelements.root = element.root) 


And the getParent() function will be treated with 

if ($criteria-&gt;ancestorOf)
{
    $query-&gt;andWhere(
        array('and',
            'structureelements.lft &lt; :ancestorOf_lft',
            'structureelements.rgt &gt; :ancestorOf_rgt',
            'structureelements.root = :ancestorOf_root'
        ),
        array(
            ':ancestorOf_lft'  =&gt; $criteria-&gt;ancestorOf-&gt;lft,
            ':ancestorOf_rgt'  =&gt; $criteria-&gt;ancestorOf-&gt;rgt,
            ':ancestorOf_root' =&gt; $criteria-&gt;ancestorOf-&gt;root
        )
    );

    if ($criteria-&gt;ancestorDist)
    {
        $query-&gt;andWhere('structureelements.level &gt;= :ancestorOf_level',
            array(':ancestorOf_level' =&gt; $criteria-&gt;ancestorOf-&gt;level - $criteria-&gt;ancestorDist)
        );
    }
}