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

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 = m开发者_运维问答ysql_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...

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);

]

I think you should restructure your data. Let the children hold the id of the parent. As it is now, I think you will have a lot of trouble building queries and even more trouble when you need to add some node because you need to recalculate the left_node and right_node values.

Here is the table structure I think you should use.

create table categories (
    category_id int primary key auto_increment,
    parent_id   int,
    name        varchar(255));

alter table categories
    add constraint FK_categories
    foreign key (parent_id)
    references categories (category_id); 

Here is the code to insert your test data. (Lets keep talking food:=) )

insert into categories (category_id, parent_id, name)
  select 1, null, 'food'   union all
  select 2, 1,    'fruit'  union all
  select 3, 2,    'red'    union all
  select 4, 3,    'cherry' union all
  select 5, 2,    'yellow' union all
  select 6, 5,    'banana' union all
  select 7, 1,    'meat'   union all
  select 8, 7,    'beef'   union all
  select 9, 7,    'pork';

fruit and meat has parent_id = 1 pointing to food etc.

The queries you need to write is a lot simpler now.

/* Get parent */
select P.name
from categories as C
    inner join categories as P
        on C.parent_id = P.category_id
where C.name = 'red';        

/* Get children */
select C.name
from categories as C
    inner join categories as P
        on C.parent_id = P.category_id
where P.name = 'red';        

/* Get siblings */
select C2.name
from categories as C1
    inner join categories as C2
        on C1.parent_id = C2.parent_id
 where C1.name = 'red';       

/* Get grandparent */
select G.name
from categories C
    inner join categories as P
        on C.parent_id = P.category_id
    inner join categories as G
        on P.parent_id = G.category_id
where C.name = 'red';        

And when you need to add the color blue you only need to do this.

insert into categories(parent_id, name) 
    values(2, 'blue');

Set parent_id for blue to 2 because that is the categoriy_id for fruit.

All nodes with same grandparent as red except the siblings of red.

select
    C2.name
from categories C
    inner join categories as P
        on C.parent_id = P.category_id
    inner join categories as P2
        on P2.parent_id = P.parent_id and
           P2.category_id <> P.category_id
    inner join categories as C2
        on P2.category_id = C2.parent_id
where C.name = 'red';        

$node_name is the node whose direct children are to be returned:

SELECT c.*
FROM categories c
  INNER JOIN categories p ON c.left_node > p.left_node AND c.right_node < p.right_node
  LEFT JOIN categories g  ON g.left_node > p.left_node AND g.right_node < p.right_node
                         AND g.left_node < c.left_node AND g.right_node > c.right_node
WHERE p.name = '{$node_name}' AND g.category_id IS NULL