First query:
SELECT integration_department_map.foreign_department_key AS 'Department Code', department.department_name AS 'Department Name'
FROM integration_department_map
JOIN department ON department.department_id = integration_department_map.department_id
WHERE integration_department_map.client_id = '10134';
Second query:
SELECT integration_department_map.foreign_department_key AS 'Department Code', location.location_name AS 'Location Name'
FROM integration_department_map
JOIN location ON location.location_id = integration_department_map.location_id
WHERE integration_department_map.client_id = '10134';
They both return the results needed separately, but I want to know if there is a way they could be written as one query?
CodePudding user response:
As suggested by honeybadger in the comments. Just add a join
to location table like:
SELECT
i.foreign_department_key AS `Department Code`,
d.department_name AS `Department Name`,
l.location_name AS `Location Name`
FROM integration_department_map i
JOIN department d
ON d.department_id = i.department_id
JOIN location l
ON l.location_id = i.location_id
WHERE i.client_id = 10134
And remove quotes around id
if it's an int
as it should be.
CodePudding user response:
Perhaps what you are looking for is a LEFT JOIN and not a JOIN (inner join). How many lines in the original integration_department_map satisfy the WHERE clause, and are they the same lines?
maybe this will do the trick -
SELECT
i.id,
i.foreign_department_key AS `Department Code`,
d.department_name AS `Department Name`,
l.location_name AS `Location Name`
FROM integration_department_map i
LEFT JOIN department d ON d.department_id = i.department_id
LEFT JOIN location l ON l.location_id = i.location_id
WHERE i.client_id = 10134