Mysql query to get records from different columns for id
Below is the details of my table structure.
Structure of table sections:
id | department
1 | Department 1
2 | Department 2
3 | Department 3
4 | Department 4
The structure of the "custom_forms_departments" table:
id | form_id | department_id | enduser_to_department | department_to_enduser
1 | 5 | 1 | Y | N
2 | 6 | 1 | N | Y
3 | 8 | 2 | Y | Y
4 | 7 | 3 | N | Y
5 | 4 | 3 | Y | N
6 | 2 | 4 | N | N
The result must be a return department_id that is "Y" for the "enduser_to_department" and "department_to_enduser" fields on the same line or on a different line.
department_id = 1 contatin value "Y" for "enduser_to_department" and "department_to_enduser" on different lines department_id = 2 contatin value "Y" for "enduser_to_department" and "department_to_enduser" on the same lines department_id = 3 contatin value "Y" for "enduser_to_department" "and" department_to_enduser "on separate lines
Result:
department_id | departments
1 | Department 1
2 | Department 2
3 | Deapartment 3
I am using the following SQL query which does not give the correct result.
SELECT departments.department_id, departments.department
FROM custom_forms_departments , departments
WHERE departments.department_id = custom_forms_departments.department_id
AND (custom_forms_departments.enduser_to_department = 'Y'
OR custom_forms_departments.department_to_enduser = 'Y')
GROUP BY departments.department_id
ORDER BY departments.department_id DESC
Please suggest this to me.
source to share
Try to take care of first custom_forms_departments
.
Path: create 2 copies custom_forms_departments
(c1 and c2). You will want to join them based on ( c1.department_id = c2.department_id
) -simple and ( c1.enduser_to_department = c2.department_to_enduser
) - because you only want to get rows that have both "Y" (will filter "Y" in WHEN, but for now you will get rows that have the same value in both columns). Second, use WHEN to filter only "Y".
SELECT
custom_forms_departments.department_id
FROM
custom_forms_departments c1
INNER JOIN
custom_forms_departments c2 ON c1.department_id = c2.department_id
AND c1.enduser_to_department = c2.department_to_enduser
WHERE
c1.enduser_to_department = 'Y'
GROUP BY
c1.department_id
;
Now that we have the "tricky" staff, let's put everything together and add columns departments
:
SELECT
departments.department_id, departments.department
FROM
departments
INNER JOIN
(SELECT
custom_forms_departments.department_id
FROM
custom_forms_departments c1
INNER JOIN custom_forms_departments c2 ON c1.department_id = c2.department_id
AND c1.enduser_to_department = c2.department_to_enduser
WHERE
c1.enduser_to_department = 'Y'
GROUP BY
c1.department_id) c3 ON departments.department_id = c3.department_id
;
source to share
Use the following query. Also always use explicitJOIN
SELECT d.department_id, d.department
FROM custom_forms_departments AS cfd
INNER JOIN departments AS d ON d.department_id = cfd.department_id
AND (cfd.enduser_to_department = 'Y' OR cfd.department_to_enduser = 'Y')
GROUP BY d.department_id, d.department
ORDER BY d.department_id
source to share