PHP multi-table query subquery statement

php sql subquery statement is to first query a result through a statement, and then query again from the result through a query statement, such as "select max(goods_id),cat_id,goods_name from goods group" by cat".

Recommended: "PHP Video Tutorial"

The so-called subquery statement , that is, first query a result through a statement, and then query again from the result through a query statement. There are generally three types of subquery statements: Let’s use a case to explain.

Case: Query the latest product information under each category in [Example 1].

If you query through a single sentence, you cannot get it. The following is wrong:

##select max(goods_id),cat_id,goods_name from goods group by cat_id;

In this way, the latest product ID number goods_id of each category is obtained, but other information such as goods_name is the old one. Therefore, if you want to obtain the latest product information for each category, you must either query separately or use a subquery.

1: where subquery statement.

select * from goods where goods_id in ( select max(goods_id) from goods group by cat_id);

In this code, we first use group by to query the latest product ID of each category, and then use the where...in () conditional statement to query the product information of the latest product ID of each category. Here is the group by statement as a subquery statement of where.

2: from subquery statement.

select * from (select * from goods order by cat_id,goods_id desc) as gk group by cat_id;

From subquery is to use the results of the select query as a table, such as the bold code above. Note: The result set queried by select must be given an alias, such as the red part in the above code. Otherwise, an error will be reported.

In this way, we also query the latest product information under each category.

3: exists subquery statement:

Title: Query [Example 1] which columns have products. Category table name: category

select * from category where exists( select * from goods where goods.cat_id=category.cat_id )

The exists check in the previous sentence (when the category ID in the product table = the category ID in the category table), is there any product? If there is, then query the information of this category. If not, exclude this category. The final result is that the product classification information of the product is queried.

Well, here we introduce three commonly used mysql database subquery statements: where subquery statement, from subquery statement, and exists subquery statement. Of course, if you don't want to use subquery statements, you can also split them into two statements for execution, and the effect will be the same.

The above is the detailed content of PHP multi-table query subquery statement. For more information, please follow other related articles on the PHP Chinese website!