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'
Best Answer
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:
Here's the results (truncated to just show the location and department data).
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 JOIN
s forcheck_assigned_areas
,areas
that are locations, andareas
that are departments, you have aLEFT JOIN
forcheck_assigned_areas
andareas
that are locations, and a separateLEFT JOIN
forcheck_assigned_areas
andareas
that are departments. In both of theseLEFT JOIN
s,check_assigned_areas
and that set ofareas
rows areINNER JOIN
ed 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 thecheck_area_id
from the location and thecheck_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.