Join 3 tables; select a list from specific group

Question

I have 3 tables

Table#1: groups

|---------------|---------------|
|  id_group     |    Name       |
|-------------------------------|
|    1          |    Group 1    |
--------------------------------|
|    2          |    Group 2    |
--------------------------------|
|    3          |    Group 3    |
--------------------------------|

Table#2: jobs

|---------------|---------------|----------------------|
|     id_job    |    id_group   |      name_job        |
|---------------|--------------------------------------|
|      1        |    1          |       name_1         |
-------------------------------------------------------|
|      2        |    1          |       name_2         |
-------------------------------------------------------|
|      3        |    2          |       name_3         |
-------------------------------------------------------|
|      4        |    3          |       name_4         |
-------------------------------------------------------|
|      5        |    3          |       name_5         |
-------------------------------------------------------|
|      6        |    3          |       name_6         |
-------------------------------------------------------|

Table#3: users

|---------------|---------------|
|  user         |    id_job     |
|-------------------------------|
|    A          |       1       |
--------------------------------|
|    B          |       1       |
--------------------------------|
|    C          |       4       |
--------------------------------|
|    D          |       6       |
--------------------------------|

If I were an user (e.g C), the result I'd look for is a page where there is a table with 2 columns (USERS | JOB).

The column USERS should be filled in with all the users who share the same group. The column JOB should be filled in with all name_job of the specific group group as follows:

         LOGIN: C
|---------------|---------------|
|  user         |       job     |
|-------------------------------|
|    C          |       name_4, |
|               |       name_5, |
|               |       name_6  |
--------------------------------|
|    D          |       name_4, |
|               |       name_5, |
|               |       name_6  |
--------------------------------|

I tried with this code, but the result is that I can read people who share the same group with me, but I can't read the jobs.

SELECT users.* jobs.* 
FROM users JOIN jobs ON users.id_job=jobs.id_job 
WHERE job.id_group IN 
(SELECT job.id_group 
FROM users JOIN jobs ON users.id_job = jobs.id_job 
WHERE users.user= '$login')

Your help is appreciated in advance.

Answer 1

If I follow you correctly, you want the list all jobs of the group the user belongs to. One option uses a correlated subquery and string aggregation:

select u.*,
    (
        select group_concat(j1.name_job order by j1.id_job)
        from jobs j
        inner join jobs j1 on j1.id_group = j.id_group
        where j.id_job = u.id_job
    ) as job_names
from users u