MySQL Select From Multiple Tables Based On Common Relationships

Question

I have 3 tables as follows:

Table: sys_department
Columns:   dept_id   dept_name
row:       1         Support
row:       2         HR
row:       3         Billing

Table: sys_groups
Columns:   group_id   group_name
row:       1          Manager
row:       2          Intern
row:       3          Staff

Table: group_dept_access
Columns:   group_id   dept_id
row:       1          3
row:       2          1
row:       2          2 
row:       3          1
row:       3          2 

I would like to run a query to fetch the list of group names with the department names they have access to.

So since group_id of 3 in group_dept_access has both 1 and 2 listed as dept_id that group would return back with it's group_name of Billing along with the department names Support and HR.

I was able to run the query to fetch the group_name when selecting group_dept_access using JOIN, but I don't know how to query all three tables and get the departments as well.

Unfortunately I can't change the table structure as it would be nice if say the 3rd table didn't have multiple entries for each group, but it's an existing system so would break if changed. Also using MySQL 5.1.x on current server, but in future will be using 5.5.x. if such a query is done differently between versions how would each be done?

Any help appreciated as this will help me make other similar queries that I will be performing. An explanation on how it's done would be great too so that I can learn how this 3 table query is done. Thank you in advance.

Answer 1

Try this:

SELECT sg.group_name, A.dept_name 
FROM sys_groups sg 
INNER JOIN (SELECT gda.group_id, GROUP_CONCAT(sd.dept_name SEPARATOR '|') dept_name
            FROM group_dept_access gda 
            INNER JOIN sys_department sd ON gda.dept_id = sd.dept_id 
            GROUP BY gda.group_id) AS A ON sg.group_id = A.group_id