Self-connection of the data table

There is a three-level structure of the data table, itself connected to see the structure of the classification, including the parent class, subclass

To create a data table:

Mysql> CREATE TABLE Tdb_goods_type (
-type_id tinyint unsigned auto_increment primary key,
-Type_name varchar (a) NOT NULL,
-parent_id tinyint NOT NULL
);

Insert data:

INSERT into ' tdb_goods_type ' VALUES (default ' programming language ', ' 0 ');
INSERT into ' tdb_goods_type ' VALUES (default ' static programming language ', ' 1 ');
INSERT into ' tdb_goods_type ' VALUES (default ' dynamic programming language ', ' 1 ');
INSERT into ' tdb_goods_type ' VALUES (Default ' JavaScript ', ' 2 ');
INSERT into ' tdb_goods_type ' VALUES (Default ' Shell ', ' 2 ');
INSERT into ' tdb_goods_type ' VALUES (Default ' Perl ', ' 2 ');
INSERT into ' tdb_goods_type ' VALUES (Default ' C ', ' 3 ');
INSERT into ' tdb_goods_type ' VALUES (default ' C + + ', ' 3 ');
INSERT into ' tdb_goods_type ' VALUES (Default ' Java ', ' 3 ');

We can see that the data table has a level three structure from the inserted data, so our problem is the parent class name of the query record and the subclass name under the parent class;

1) Querying the parent class name

Mysql> Select s.type_id sid,s.type_name sname,p.type_name pname
From Tdb_goods_type S
-Left JOIN Tdb_goods_type p on p.type_id=s.parent_id;

Use the parent_id field of the child table to leftist the type_id field of the parent table;

2) Query the subclass name under the parent class

Mysql> Select p.type_id pid,p.type_name pname,s.type_name sname
From Tdb_goods_type P
-S on p.type_id=s.parent_id, left join Tdb_goods_type;

Use the type_id field of the parent table to leftist the parent_id field of the child table;

The above two SQL appear to be the same, but in understanding need to introduce the concept of ' parent table ' and ' child table ';

Self-connection of the data table