Looking for a query to display nodes in the same level only

Question

The image above shows a Nested model tree approach. i am searching for a query that will only display level number 1 with nodes FRUIT and MEAT when FRUIT or MEAT are called

I

I have formulate a combination of children and sibilings I think to pull of Beef when Red is called as below.

 INSERT 



 $sqlinsert = "INSERT INTO categories
(categories_id, parent_id,name, parent)
VALUES('','','$name', '$parent')"; 
$enterquery = mysql_query($sqlinsert) or die(mysql_error()); 
$customer_id = mysql_insert_id();

I am looking to insert parent_id throug relating a new filled called parent that will be related to the existing field called "name" and then if parent field = to existing name field then take category_id from that name field and put it as the parent_id of the new INSERTED name.

for instance a user insert name "blue" and parent "food" then the name blue will take the categories id of food, and place it as the parent_id of blue...

Answer 1

Are you sure you want left_node and right_node NOT NULL? What value do you use for a leaf (e.g., banana)? Doesn't matter for this question.

You appear to be using MySQL, which does not implement the best way to answer this question. You want a recursive CTE. However, as a hack, you can do something like this:

CREATE VIEW parents AS 
SELECT c.category_id AS parent, 
 c2.category_id AS child
FROM categories c JOIN categories c2 
ON c2.category_id=c.left_node OR c2.category_id=c.right_node;

You can now get the first level with

SELECT child FROM parents
WHERE parent NOT IN (SELECT child FROM parents);

The NOT IN subquery returns the root. To get an arbitrary level, as far as I know, you need a recursive or iterative procedure or a database that allows recursive CTE, or more horrid views.

[edit: you can do this in one hard-to-read SELECT

SELECT child FROM parents
    WHERE parent NOT IN (SELECT child FROM
        (  SELECT c.category_id AS parent, 
     c2.category_id AS child
    FROM categories c JOIN categories c2 
    ON c2.category_id=c.left_node OR c2.category_id=c.right_node) AS anyname);

]