MySQL query method with multiple conditions

mysql query with multiple conditions

Environment: MySQL 5.7

When multiple and or ors appear in a where statement, you need to enclose multiple ORs in parentheses and then combine them with AND, or enclose multiple ANDs in parentheses and then combine them with OR.

mysql many-to-many conditional query

Two tables user and role, the middle table is user_role

Query the correspondence between users and roles

select res.user_name,r.role_name from(select u.user_name,ur.role_id from user as u INNER JOIN user_role as ur where u.user_id=ur.user_id) as res 
INNER JOIN role as r where res.role_id=r.role_id;

mysql query to find multiple conditions in the same field

analyze:

1. First, find out the courses that student No. 01 is studying

SELECT C FROM sc WHERE S='01' 

2. Query the student numbers of the relevant courses

SELECT S FROM sc WHERE C in 
(SELECT C FROM sc WHERE S='01') 

As you can see, we queried three times, so there are multiple results. Because it is an or relationship, all students who selected courses 1, 2, and 3 are retrieved.

3. Now we need to take out the number that appears 3 times

SELECT S FROM sc WHERE C in 
(SELECT C FROM sc WHERE S='01') 
GROUP BY S HAVING COUNT(S) = 3 

I saw that students numbered 1, 2, 3, and 4 chose the same course as student numbered 01.

4. Perform a join query with the student table to retrieve relevant information

SELECT a.* FROM
student a LEFT JOIN sc b
ON aS = bS WHERE bS in (
SELECT S FROM sc WHERE C in 
(SELECT C FROM sc WHERE S='01') 
GROUP BY S HAVING COUNT(S) =3)
GROUP BY aS 

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.