MySQL where + If judgment

In MySQL, if is determined based on multiple conditions in where, the project requirements are as follows:
In a friend relationship, there are three roles, namely A, B, and C. The types of friends are dynamic: 1, 2, 3, 4, and 5. A is a required role. If a has a role, you can check the dynamic type 1, 3, and 4 of friends. If B has a role, you can check 2 types of information. If a has a C role, you can check 5 types of roles.
The implementation idea is as follows: first identify all the dynamic data of the friend relationship-"Judge B role to add filter 2 type-" Judge C role to add filter 5 type
The following describes how to implement step aggregation. The added data is not described here.
1. Add a relational table

Copy content from SQL code to clipboard

1. --
2. -- Table structure 'table'
3. --
5. Create Table if not exists 'table '(
6. 'Uid' int (11) not null auto_increment comment 'follow id ',
7. 'User _ id' int (11) not null comment 'user name id ',
8. 'User _ follow_id 'int (11) not null comment' user ID to be followed ',
9. 'Iscarer' tinyint (2) not null default '0' comment' role a follow friend 1 is 0 No 2 ignore ',
10. 'Ishelper 'tinyint (2) not null default '0' comment' Role B follow Master 1 is 0 No 2 ignore ',
11. 'Isprovision' tinyint (2) not null default '0' comment' role C follow service provider 1 is 0 No 2 ignore ',
12. 'Insert _ time' varchar (20) not null comment' increase time ',
13. Primary Key ('uid ')
14. ) Engine = MyISAM default charset = utf8 comment = 'user relationship follow table' auto_increment = 21;

2. Add a friend dynamic table

Copy content from SQL code to clipboard

1. --
2. -- Table structure 'table B'
3. --
5. Create Table if not exists 'table B '(
6. 'Uid' int (11) not null auto_increment comment 'message id ',
7. 'Type' tinyint (4) not null comment 'type 1 car owner article 2, follow the article, 3 ask a question, 4, investigate, 5 order information ',
8. 'User _ id' int (11) not null,
9. 'Event _ message' text not null comment 'dynamic data serialization ',
10. 'Insert _ time' varchar (20) not null,
11. Primary Key ('uid ')
12. ) Engine = MyISAM default charset = utf8 comment = 'friends dynamic table' auto_increment = 3;

3. The dynamic record of friends is as follows:
The SQL statement is as follows:

Copy content from SQL code to clipboard

1. Select
2. *
3. From
4. Table A F
5. Left join (
6. Select
7. Type,
8. User_id as author_id,
9. Event_message
10. From
11. Table B
    
12. ) E on F. user_follow_id = E. author_id
13. Where
14. User_id = 275
15. And iscarer = 1
16. And type is not null

Result 3:

4. Add Role B to judge the SQL statement as follows:

Copy content from SQL code to clipboard

1. Select
2. *
3. From
4. Table A F
5. Left join (
6. Select
7. Type,
8. User_id as author_id,
9. Event_message
10. From
11. Table B
    
12. ) E on F. user_follow_id = E. author_id
13. Where
14. If (ishelper = 0, type <> 2, '1 = 1 ')
15. And user_id = 275
16. And iscarer = 1
17. And type is not null

Result 2:

4. Add the C role to judge the SQL statement as follows:

Copy content from SQL code to clipboard

1. Select
2. *
3. From
4. Table A F
5. Left join (
6. Select
7. Type,
8. User_id as author_id,
9. Event_message
10. From
11. Table B
    
12. ) E on F. user_follow_id = E. author_id
13. Where
15. If (ishelper = 0, type <> 2, '1 = 1 ')
16. And
17. If (issupplier = 0, type <> 5, '1 = 1 ')
18. And user_id = 275
19. And iscarer = 1
20. And type is not null

Result 1:

Well, we have achieved the desired effect.
PS: The following where statements can be sorted by index value.

Copy content from SQL code to clipboard

1. Where
2. User_id = 275
3. And iscarer = 1
4. And type is not null
5. And
6. If (ishelper = 0, type <> 2, '1 = 1 ')
7. And
8. If (issupplier = 0, type <> 5, '1 = 1 ')