Problem with MySQL multiple join on same table with condition

Question

Using MySQl 5.6 as my database, I'm trying to join a table twice, on another joined table, but I'm getting duplicate results, rather than the row combined.

SO, I have a checks table, and a check can be assigned to an area or areas, which are stored in the check_assigned_area table. Entries in this table can either be a location or a department. Locations being a parent of departments.

SO, im joining the checks_assigned_area table on to the checks table via the PK (check_id)

AND then im trying to join the areas table twice, once for locations and once for departments, depending on the area_level. 3 being a location and 4 being a department.

But instead of the query returning a single row with the location name and department name. Its returning 2 seperate rows, and it isnt populating the department name.

I've tried all manner of left/inner/right join combinations, distincts, group by's, but I cant get the data correct.

Can I even do it like this? My logic tells me yes, but practically I cant get it to work.

Any ideas if I can get it to output the data i need?

Heres an SQL fiddle of my data and query http://sqlfiddle.com/#!9/63d6ce/4

Checks table:

+----------+---------------+------------+------------+--------+----------+
| check_id | check_type_id | due | created_by | status | sign_off |
+----------+---------------+------------+------------+--------+----------+
| 19335 | 43 | 18/09/2017 | 3 | 1 | 1 |
| 19336 | 4 | 19/09/2017 | 3 | 1 | 1 |
| 19358 | 62 | 21/09/2017 | 47336 | 1 | 1 |
+----------+---------------+------------+------------+--------+----------+

Check assigned area table:

+------------------------+---------------+----------+
| check_assigned_area_id | check_area_id | check_id |
+------------------------+---------------+----------+
| 18801 | 9 | 19335 |
| 18802 | 816 | 19336 |
| 18803 | 816 | 19336 |
| 18804 | 8091 | 19358 |
| 18835 | 8092 | 19358 |
+------------------------+---------------+----------+

Query:

SELECT `c`.*, `t`.`check_name`,`a`.`check_area_id`,`l`.`area_name` AS 'location_name', `d`.`area_name` AS 'dept_name'
FROM `checks` `c`
LEFT JOIN `check_assigned_areas` `a` ON `a`.`check_id` = `c`.`check_id`
LEFT JOIN `areas` `l` ON `l`.`area_id` = `a`.`check_area_id` AND `l`.`area_level` = '3'
LEFT JOIN `areas` `d` ON `d`.`area_id` = `a`.`check_area_id` AND `d`.`area_level` = '4'
LEFT JOIN `check_types` `t` ON `t`.`check_type_id` = `c`.`check_type_id`
WHERE `c`.`org_id` = '297'
AND `c`.`status` != '0'
AND `c`.`due` <= '2017-09-21'

• Artashes Khachatryan: You already have duplicate rows in check_area_assigned table. CheckID 19336 is assigned twice to area 816 and once to 8086.
• frobak: I've updated the above tables data so it new correspones to the fiddle

Answer 1

I tweaked the fiddle you provided, and I believe I now get the results you wanted. Here's the updated fiddle.

The updated query is:

SELECT `c`.*, `t`.`check_name`,`al`.`check_area_id` as 'loc_check_area_id',`l`.`area_name` AS 'location_name',`ad`.`check_area_id` as 'dept_check_area_id', `d`.`area_name` AS 'dept_name'
FROM `checks` `c`
LEFT JOIN (`check_assigned_areas` `al` 
 INNER JOIN `areas` `l` ON `l`.`area_id` = `al`.`check_area_id` AND `l`.`area_level` = '3'
 ) ON `al`.`check_id` = `c`.`check_id`
LEFT JOIN (`check_assigned_areas` `ad` 
 INNER JOIN `areas` `d` ON `d`.`area_id` = `ad`.`check_area_id` AND `d`.`area_level` = '4'
 ) ON `ad`.`check_id` = `c`.`check_id`
LEFT JOIN `check_types` `t` ON `t`.`check_type_id` = `c`.`check_type_id`
WHERE `c`.`org_id` = '297'
AND `c`.`status` != '0'
AND `c`.`due` <= '2017-09-21'
;

Here's the results (truncated to just show the location and department data).

 check_id | ... | loc_check_area_id | location_name | dept_check_area_id | dept_name
----------+ ... +-------------------+---------------+--------------------+--------------
 19358 | ... | 8091 | Luton Office | 8092 | Development
 19357 | ... | (null) | (null) | (null) | (null)

Since there are two separate check_assigned_areas rows for the check that has a valid location and department, you would logically get two rows for that check. One row represents a location, one a department. So, you got separate rows for those values.

I changed the query, so instead of separate LEFT JOINs for check_assigned_areas, areas that are locations, and areas that are departments, you have a LEFT JOIN for check_assigned_areas and areas that are locations, and a separate LEFT JOIN for check_assigned_areas and areas that are departments. In both of these LEFT JOINs, check_assigned_areas and that set of areas rows are INNER JOINed together.

This means that you'll get one (set of) row(s) for the location(s) that tie to this check, and one for the department(s).

Note that there are two check_assigned_areas.check_area_id values involved, so I show the check_area_id from the location and the check_area_id from the department as two separate columns.

Also note that, if there are multiple areas that are locations tied to the check, and/or multiple departments, you would see a row for each possible combination of location and department found; that's why the previous paragraph is written to allow for such.

However, if there's zero or one locations, and zero or one departments, you'll get one row with the location and department found.

• frobak: Thank you, that works brilliantly. And thank you for spending the time to explain the issues and why I was getting the results