如何使用来自不同表的数据来获取MySQL中的相应记录

I have currently got a PHP generated calendar displaying some holidays for users. This information is stored in a database, I.e holidays and users. I want a user to be able to select a department and then AJAX will load the holidays for users only in that department.

Here are two made up tables with the same fundamental structure:

Table users

+------------------------------------+
| User       | Department            |
|------------+-----------------------|
| Brian      | Sales                 |
| Tony       | Marketing             |
| Carol      | Marketing             |
| Dave       | Warehouse             |
| Chris      | Warehouse             |
+------------------------------------+

Table holiday

+------------------------------------+
| ID         | User                  |
|------------+-----------------------|
| 1          | Dave                  |
| 2          | Tony                  |
| 3          | Tony                  |
| 4          | Chris                 |
| 5          | Carol                 |
+------------------------------------+

My current query:

$getAllHols = $con->query("SELECT * FROM `holiday`");

So of course, this just gets all holiday. I'm knowledgable enough on PHP to get a list of users in a specific department and then use that in another query to get holidays for those users. But I don't want to do this. I'm thinking there MUST be a pure-SQL solution. So I need the query to get all records from holiday where the user is in the selected department, using the two tables.

I.E:

If I chose the department "Marketing", records 2, 3 and 5 would be returned. (Tony and Carol are in Marketing).

Very easy problem for an advanced SQL user, I'm sure, but not to me. Can anyone help? Thanks.

Try this.

SELECT * FROM users 
LEFT JOIN holiday ON users.user = holiday.user
WHERE holiday.department = 'marketing'

As far as I got...

select user
from users inner join holiday
on users.user = holiday.user
where department = 'Marketing'

This would provide a distinct list of records from the Holiday table if there are any matching records from the Users table. This improves upon the option of joining the tables, as you would not have to worry about de-duping the resulting data.

select distinct h.id, h.user
  from holiday h
 where h.user in (select u.user
                    from user u
                   where u.department = 'Marketing')
 ;