SQL query that considers data from other table-CodePudding

I have two tables:

employees:

id, CMS_user_id, practice_group_id, ...

and

users:

id, level, ...

I want to select all employees where practice_group_id is 2 but only if the respective user has a level of 1 according to the users table. I researched and I have a feeling it has something to do with the UNION keyword eventually, but I can't quite figure it out.

In "human language", the query would be like this: "select all from employees where practice_group_id is 2 and then check the CMS_user_id from the employee and check in the table users whether the respective user with the id that equals CMS_user_id has a level of 1"

CodePudding user response：

A JOIN will match the corresponding rows between two tables. Then, filtering can be done using WHERE.

For example:

select e.*
from employees e
join users u on u.id = e.CMS_user_id
where e.practice_group = 2 and u.level = 1