Is There a Simple Way to Query the Children of a Node

Is there a simple way to query the children of a node?

Did you read the article you posted? It's under the heading "Find the Immediate Subordinates of a Node"

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'PORTABLE ELECTRONICS'
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

However, what I do (this is cheating) is I combined the nested set with adjacency lists -- I embed a "parent_id" in the table, so I can easily ask for the children of a node.

Query to find all the parent and child nodes of a given node in SQL

This should work:

;WITH #results1 AS
(
    SELECT  ChildId, 
            ParentId 
    FROM    History
    WHERE   ChildId= @selected
    UNION ALL
    SELECT  t.ChildId, 
            t.ParentId 
    FROM    History t
            INNER JOIN #results1 r ON r.ExpirationList = t.ParentId 
)
,#results2 AS
(
    SELECT  ChildId, 
            ParentId 
    FROM    History
    WHERE   ChildId= @selected
    UNION ALL
    SELECT  t.ChildId, 
            t.ParentId 
    FROM    History t
            INNER JOIN #results2 r ON t.ExpirationList = r.ParentId 
)
SELECT  *
FROM    #results1
UNION
SELECT  *
FROM    #results2

How to query the existing children of a sibling node and loop over them to detect if it exists?

This question originated two different approaches. Please find them here:

1) By Renaud Tarnec using the Cloud Function

This should work, but I haven't test it. You may need to test is insBefore is undefined in addition to testing it is null. I let you fine tune it.

let insAfter;
let roomPushKey ;
exports.updateRoomIns = functions.database.ref('/doors/{MACaddress}').onWrite((change, context) => {
    const afterData = change.after.val(); // data after the write
    roomPushKey = afterData.inRoom;
    insAfter = afterData.ins;
    return admin.database().ref('/rooms/' + roomPushKey).once('value').then(snapshot => {
    const insBefore = snapshot.val().ins;
    const updates = {};
    if (insBefore === null || insBefore === undefined ) {
        Object.keys(insAfter).forEach(key => {
           updates['/rooms/' + roomPushKey + '/ins/' + key] = true;
        });
    } else {
        Object.keys(insAfter).forEach(key => {
           if (insBefore.hasOwnProperty(key)) {
              updates['/rooms/' + roomPushKey + '/ins/' + key + '_a'] = true; 
           } else {
              updates['/rooms/' + roomPushKey + '/ins/' + key] = true;
           }
        });
    }
    return admin.database().ref().update(updates);
    });
});.catch(error => {
    console.log(error);
    //+ other error treatment if necessary

});

2) the second approach was a more direct one

I have decided to solve it on the Arduino program, because all doors have different MAC Addresses I simply added the MAC address to the end of of the timestamp and because no door will register two ins at the same time that did the trick for me.

Neo4j How to query to all children nodes

Perhaps you are looking for:

MATCH (u:UserNode{promoterName:"Antal"})-[:PROMOTER_OF*]->(c:UserNode)
RETURN c;

You can add a number together with the asterisk (e.g. PROMOTER_OF* 3) or a range (e.g. PROMOTER_OF* 1..5).
If you have a lot of data I do not recommend using a bare asterisk.

Get all child nodes from table

It is because you need the same where statement on the bottom select for tblcategory WHERE [pkID] = 6. With out it you are getting the children of the entire table.

So something like this should work:

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
UNION ALL
SELECT [Sub].[pkID]
    ,[Sub].[ParentID]
    ,[Sub].[CategoryName]
FROM [tblCategory] [Sub]
INNER JOIN [tblCategory] AS [Sub2] ON [Sub].[ParentID] = [Sub2].[pkID]
WHERE [sub].[pkID] = 6

Simplified as Juan noted and I should have thought of can be done as a single where condition on the same select statement.

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
     or ParentId = 6

Here is a recursive cte method to get all of the children, grand children, great grand children, etc. of a parentid.

;WITH cte AS (
    SELECT [pkID]
       ,[parentID]
       ,[CategoryName]
       -- ,1 AS [Level]
    FROM [tblCategory]
    WHERE [pkID] = 6

    UNION ALL

    SELECT
       t.pkId
       ,t.[parentID]
       ,t.CategoryName
       -- ,[Level] + 1 AS [Level]
    FROM
       [tblCategory] t
       INNER JOIN cte c
       ON t.ParentId = c.pkId
)

SELECT *
FROM cte  

Getting all the children (and their children) of a given parent node in a MySQL relational table

CREATE PROCEDURE get_childs ()
BEGIN
CREATE TABLE get_childs_tmp(id INT UNIQUE, level INT) ENGINE = Memory;
INSERT INTO get_childs_tmp SELECT child, 0 FROM test WHERE child = parent;
REPEAT
    INSERT IGNORE INTO get_childs_tmp
    SELECT test.child, get_childs_tmp.level + 1
    FROM get_childs_tmp
    JOIN test ON test.parent = get_childs_tmp.id;
UNTIL !ROW_COUNT() END REPEAT;
SELECT * FROM get_childs_tmp WHERE level > 0;
DROP TABLE get_childs_tmp;
END

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c5933bfa05414cd68c91c318a727ed6f

---

Related Topics