SQL get all items that belong to the same user group of the requesting user

Question

I wish figure out how to get all appointments that belong to the same user group of the requesting user in just one SQL query. My only data is the user_id of the requesting user.

Minimal reproducable example:

appointments
----------------
id    user_id
1    2
2    3
3    1
4    2
5    2

users
----------------
id    group_id
1    5
2    8
3    8

Task: I'm user 3 and want to get all appointments that are from users in my group. user 3 is in group 8, so is user 2. appointments that belong to user 2 and 3 are: 1,2,4,5

What I have done so far, is using two SQL statements:

SELECT group_id
FROM users
WHERE id = 3

then knowing my group_id:

SELECT a.*
FROM appointments a
JOIN users u ON a.user_id = u.id
WHERE u.group_id = my_group_id

Answer 1

Probably this is query what you need:

SELECT a.*                       -- get all appointments data
FROM appointments a              -- for users 
JOIN users u ON a.user_id = u.id -- in group
WHERE u.group_id = (             -- where requested user is
    SELECT group_id
    FROM users
    WHERE users.id = '<User ID what you need>' -- Replace with an actual user ID
);

Test this query online

Answer 2

There are a few ways to do this, the previous answer with an inner select is fine, for readability consider this instead with an extra join to allow you to have a single where clause:

SELECT a.*
FROM appointments a
JOIN users u1 ON a.user_id = u1.id
JOIN users u2 ON u1.group_id = u2.group_id
WHERE u2.id = 3;