Comment interroger une table MySQL pour afficher la racine et sa subchild.

Eh bien, pas une mise en œuvre assez propre, mais que vous n'avez que les enfants et les sous-enfants, l'une de ces personnes pourrait fonctionner:

Query1: P>

SELECT UserID, UserName
FROM tbl_user 
WHERE UserID = 3
OR ParentID = 3
OR ParentID IN (SELECT UserID
    FROM tbl_user
    WHERE ParentID = 3);

Il est techniquement possible de faire des requêtes hiérarchiques récursives dans MySQL à l'aide de procédures stockées.

Voici une adaptation à votre scénario: P>

CREATE TABLE `user` (
  `UserID` int(16) unsigned NOT NULL,
  `UserName` varchar(32),
  `ParentID` int(16) DEFAULT NULL,
  `TopID` int(16) DEFAULT NULL,
  PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user VALUES (1, 'abc', NULL, NULL), (2, 'edf', 1, 1), (3, 'gef', 1, 1), 
 (4, 'huj', 3, 1), (5, 'jdi', 4, 1), (6, 'das', 2, 1), (7, 'new', NULL, NULL), 
 (8, 'gka', 7, 7);

DELIMITER $$
DROP PROCEDURE IF EXISTS `Hierarchy` $$
CREATE PROCEDURE `Hierarchy` (IN GivenID INT, IN initial INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE next_id INT;

    -- CURSOR TO LOOP THROUGH RESULTS --
    DECLARE cur1 CURSOR FOR SELECT UserID FROM user WHERE ParentID = GivenID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- CREATE A TEMPORARY TABLE TO HOLD RESULTS --
    IF initial=1 THEN
        -- MAKE SURE TABLE DOESN'T CONTAIN OUTDATED INFO IF IT EXISTS (USUALLY ON ERROR) --
        DROP TABLE IF EXISTS OUT_TEMP; 
        CREATE TEMPORARY TABLE OUT_TEMP (userID int, UserName varchar(32));
    END IF;

    -- ADD OURSELF TO THE TEMPORARY TABLE --
    INSERT INTO OUT_TEMP SELECT UserID, UserName FROM user WHERE UserID = GivenID;

    -- AND LOOP THROUGH THE CURSOR --
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO next_id;

        -- NO ROWS FOUND, LEAVE LOOP --
        IF done THEN
        LEAVE read_loop;
        END IF;

        -- NEXT ROUND --
        CALL Hierarchy(next_id, 0);     
    END LOOP;

    CLOSE cur1;

    -- THIS IS THE INITIAL CALL, LET'S GET THE RESULTS --
    IF initial=1 THEN
    SELECT * FROM OUT_TEMP;
        -- CLEAN UP AFTER OURSELVES --
        DROP TABLE OUT_TEMP; 
    END IF;
END $$
DELIMITER ;

CALL Hierarchy(3,1);
+--------+----------+
| userID | UserName |
+--------+----------+
|      3 | gef      |
|      4 | huj      |
|      5 | jdi      |
+--------+----------+
3 rows in set (0.07 sec)

Query OK, 0 rows affected (0.07 sec)

CALL Hierarchy(1,1);
+--------+----------+
| userID | UserName |
+--------+----------+
|      1 | abc      |
|      2 | edf      |
|      6 | das      |
|      3 | gef      |
|      4 | huj      |
|      5 | jdi      |
+--------+----------+
6 rows in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)