Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of Mysql self-join query. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Self-join query

Imagine the following scenario: an e-commerce website wants to make a hierarchical classification of the products in the site, there are several subcategories under one category, and there will be other subcategories under the subcategory. For example, under this category of digital products, there are notebooks, desktops, smartphones, etc.; notebooks, desktops, smartphones can be classified by brand; brands can be classified by price, and so on. Perhaps these classifications will reach a very deep level, showing a tree-like structure. So how do you represent this data in the database? We can create two fields in the database to store the id and category name, use the third field to store the id of the category's subclass or parent class, and finally query the desired results through self-connection.

A self-join query is actually equivalent to a join query, requiring two tables, except that its left table (parent table) and right table (child table) are both their own. When you do a self-join query, you connect with yourself, give two different aliases to the parent table and the child table, and then attach the connection conditions. Look at the following example:

1. Create a datasheet:

Create table tdb_cates (id smallint primary key auto_increment, cate_name varchar (20) not null, parent_id smallint not null)

Note: cate_name represents the name of the category, and parent_id represents the id of the parent class.

two。 Insert data:

Insert into tdb_cates (cate_name, parent_id) values ('digital products', 0); insert into tdb_cates (cate_name, parent_id) values ('household products', 0); insert into tdb_cates (cate_name, parent_id) values ('notebook', 1); insert into tdb_cates (cate_name, parent_id) values ('smartphone', 1); insert into tdb_cates (cate_name, parent_id) values ('appliances', 2) Insert into tdb_cates (cate_name, parent_id) values ('Furniture', 2); insert into tdb_cates (cate_name, parent_id) values ('refrigerator', 5); insert into tdb_cates (cate_name, parent_id) values ('washing Machine', 5); insert into tdb_cates (cate_name, parent_id) values ('Automobile Brand', 0); insert into tdb_cates (cate_name, parent_id) values ('Buick', 9) Insert into tdb_cates (cate_name, parent_id) values ('BMW', 9); insert into tdb_cates (cate_name, parent_id) values ('Chevrolet', 9); insert into tdb_cates (cate_name, parent_id) values ('home textile', 0)

3. Query all categories and their parent classes: suppose there are two tables (both tdb_cates), the left table is the child table, and the right table is the parent table; query the id of the child table, the cate_name of the child table, and the cate_name; join condition of the parent table is that the parent_id of the child table is equal to the id of the parent table.

The code is as follows:

Select s.id, s.cate_name, p.cate_name from tdb_cates s left join tdb_cates p on s.parent_id=p.id

4. Query all categories and subcategories: or imagine that there are two tables (both tdb_cates), the left table is the child table, and the right table is the parent table; query the id of the child table, the cate_name of the child table, and the cate_name; join condition of the parent table is that the id of the child table is equal to the parent_id of the parent table.

The code is as follows:

Select s.id, s.cate_name, p.cate_name from tdb_cates s left join tdb_cates p on p.parent_id=s.id

This is the end of this article on "sample Analysis of Mysql self-join query". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.