Search query to display nodes at only one level

Question

The figure above shows the approach with a nested model tree. I am looking for a query that will only display a level 1 number with FRUIT and MEAT nodes when FRUIT or MEAT are called

I

I formulated a combination of children and sibling, which I think to pull out 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 want to insert parent_id related to the newly populated called parent that will be associated with an existing field called "name", and then if the parent field = is in the existing name field, then take the category_id from this name field and put it as the parent_id of the new named INSERTED.

, "" "", blue id parent_id ...

Answer 1

, . . , , , node, left_node right_node.

, , , .

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

. ( : =))

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

parent_id = 1, ..

, , .

/* 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';        

, .

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

parent_id 2, categoriy_id .

, red, 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';        

Answer 2

, , left_node right_node NOT NULL? (, )? .

, MySQL, . CTE. , , - :

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;

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

NOT IN . , , , CTE .

[edit: 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);

]

Answer 3

$node_name is a node whose direct children should 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