How to solve the duplication problem in mysql using left join connection

Mysql uses left join to connect duplicates

Problem description

When using join query, for example, table A is the main table , left connection to table B. What we expect is that the query result will have as many records as there are in table A. However, there may be a result that the total number of records queried is more than the total number of records in table A. , and when the query results are displayed, some columns are repeated. Simply put, a Cartesian product is generated.

Question example

Table A is the user table (user), and the fields are:

ID name userid
1 aaaa 10001
2 bbbb 10002
3 ccccc 10003

The B table is the first type of product table (product), and the fields are:

ID title time userid
1 Title 1 2014-01-01 10002
2 Title 2 2014-01-01 10002
3 Title 3 2014-01-01 10001
4 Title 4 2018-03-20 10002
5 Title 5 2018-03-20 10003

At this time, when we used the following sql to execute, we found that

selecct * from user left join product on user.userid=product.userid;

The execution result was actually higher than the total number of records in the user table Many

Solutions to the problem

In fact, this problem can be seen by a discerning person at a glance. Because the keywords of the left join are not unique in the product table, this part of the non-unique data generates a Cartesian product. , resulting in more execution results than expected.

The solution is to use unique keys to associate and do link queries

When mysql uses left join, the data in the right table has duplicate data

LEFT JOIN keyword will Returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2). At this time, there is no problem if the right table (table_name2) has no data or only one piece of data after being filtered by the on keyword.

What I want to talk about is how to deal with duplicate data (complete duplication in business) in the right table (table_name2).

When the right table (table_name2) is filtered by the on keyword and duplicate data appears, the data found at this time will be, right table data * duplicate data, other conditional data in the right table, and the data we need The number of items is different.

My solution is to first query the right table (table_name2) according to the filter field grouping, filter out the same data, and then treat the result as the right table for association

前面脑补 
LEFT JOIN (SELECT MODEL_CODE,MODEL_NAME from tm_model GROUP BY MODEL_CODE) tm on tav.model_code = tm.MODEL_CODE 
后面脑补

The above is the detailed content of How to solve the duplication problem in mysql using left join connection. For more information, please follow other related articles on the PHP Chinese website!