Mysql left join the right table data is not unique solution, mysqljoin

Mysql left join the right table data is not unique solution, mysqljoin
1. Basic left join usage

Mysql left join statement format

A left join B ON condition expression

Left join is based on table A. Table A is the left table, and table B is the right table.

All records in the left table (A) are displayed, while in the right table (B), only records meeting the condition expression are displayed. If no matching record exists in the right table (B, NULL is the place where the record is insufficient.

For example:NewsAndNews_categoryThe table structure is as follows. The category_id of the news table corresponds to the id of the news_category table.

News table

Id	Title	Category_id	Content	Addtime	Lastmodify
1	Fashion news title	1	Fashion news content	12:00:00	12:00:00
2	Sport news title	2	Sport news content	12:00:00	12:00:00
3	Life news title	3	Life news content	12:00:00	12:00:00
4	Game news title	4	Game news content	12:00:00	12:00:00

News_category table

Id	Name
1	Fashion
2	Sport
3	Life

Displays the news Table Record and news category name. The query statement is as follows:

select a.id,a.title,b.name as category_name,a.content,a.addtime,a.lastmodify from news as a left join news_category as b on a.category_id = b.id;

The query result is as follows:

Id	Title	Category_name	Content	Addtime	Lastmodify
1	Fashion news title	Fashion	Fashion news content	12:00:00	12:00:00
2	Sport news title	Sport	Sport news content	12:00:00	12:00:00
3	Life news title	Life	Life news content	12:00:00	12:00:00
4	Game news title	NULL	Game news content	12:00:00	12:00:00

BecauseNews_categoryThe table does not have a record with id = 4, soNewsCategory_name = NULL for records whose category_id = 4 in the table

When left join is used, the number of records displayed in table A and table B is or. All records in Table A are displayed, and only matching records are displayed in table B.

2. left join right table data is not unique solution

HoweverIf the number of qualified records in Table B is greater than 1The result of left join is displayed,The number of records is more than the number of records in table..

For example:MemberAndMember_login_logThe table structure is as follows. member records member information, and member_login_log records member's daily logon records. The id of the member table corresponds to the uid of the member_login_log table.

Member table

Id	Username
1	Fdipzone
2	Terry

Member_login_log table

Id	Uid	Logindate
1	1	2015-01-01
2	2	2015-01-01
3	1	Septem
4	2	Septem
5	2	2015-01-03

Query the member user information and the last logon date:

If left join is used directly

select a.id, a.username, b.logindatefrom member as a left join member_login_log as b on a.id = b.uid;

Because member_login_log has more matching records than the member table (a. id = B. uid), the final record is:

Id	Username	Logindate
1	Fdipzone	2015-01-01
1	Fdipzone	2015-01-02
2	Terry	2015-01-01
2	Terry	2015-01-02
2	Terry	Septem

However, this is not the expected result. Therefore, we need to ensure that the qualified records of Table B are null or unique. We can use group by to implement this.

select a.id, a.username, b.logindatefrom member as a left join (select uid, max(logindate) as logindate from member_login_log group by uid) as bon a.id = b.uid;

Id	Username	Logindate
1	Fdipzone	2015-01-02
2	Terry	2015-01-03

Summary:The relationship between two tables that use left join is preferably or. This ensures that all records of Table A are displayed, and that all records that meet the conditions are displayed in table B.

If the qualified records in Table B are not unique, check whether the table design is reasonable.