Clever Geek Handbook

How to use data from another table to get matching records in MySQL

I have a PHP generated calendar that displays some holidays for users. This information is stored in a database, Ie holidays

and users

. I want the user to be able to select a department and then AJAX will load holidays for users in that department only.

Here are two tables compiled 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 request:

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

So, of course, it's just all the holidays. I am knowledgeable enough about PHP to get a list of users in a specific department and then use that in another query to get vacation for those users. But I don't want to do this. I think it MUST be a pure SQL solution. So I need a query to get all vacation records where the user is in the selected department using two tables.

i.e:

If I select Marketing, records 2, 3, and 5 are returned. (Tony and Carol in Marketing).

Very simple problem for advanced SQL user, I'm sure, but not for me. Can anyone please help? Thank.

+3


source to share


3 answers


Try it.



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


source


As far as I understand...



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


source


This will provide a separate list of records from the Holiday table if there are matching records in the Users table. This improves the ability to join tables since you don't have to worry about dropping duplicate result data.

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


source






More articles:


All Articles