CODEWITHSUNDEEP
mysqljoin
pratik
pratik

Reputation: 185

Mysql query to get records from different columns for the id

Following is the details for my table structure.

Table structure "departments":

id  |   department
1   |   Department 1
2   |   Department 2
3   |   Department 3
4   |   Department 4

Table structure "custom_forms_departments":

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

Result should be return department_id which have the vaue "Y" for field "enduser_to_department" and "department_to_enduser" in same row or in different row.

department_id = 1 contatin value "Y" for "enduser_to_department" and "department_to_enduser" in different rows department_id = 2 contatin value "Y" for "enduser_to_department" and "department_to_enduser" in same rows department_id = 3 contatin value "Y" for "enduser_to_department" and "department_to_enduser" in different rows

Result:

  department_id | departments
    1           |   Department 1
    2           |   Department 2
    3           |   Deapartment 3

I am using following SQL query which is not giving proper 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 me for this one.

Upvotes: 3

Views: 67

Answers (3)

Captain Crunch
Captain Crunch

Reputation: 587

First try to take care of custom_forms_departments.

Way: Create 2 copies of custom_forms_departments (c1 and c2). You'll want to JOIN them, based on (c1.department_id = c2.department_id) -simple, and (c1.enduser_to_department = c2.department_to_enduser) - because you want to get only rows that have both 'Y' in them (will filter the 'Y' in the WHEN, but for now, you'll get any rows that have same value in both columns). Second, use the 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 we've got the "complicated" staff, lets gather all together and add the departments columns:

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
;

Upvotes: 1

jparaya
jparaya

Reputation: 1339

SELECT id as department_id, department from departments WHERE id IN 
(
SELECT DISTINCT(departament_id) FROM custom_forms_departments WHERE enduser_to_department = 'Y' OR department_to_enduser = 'Y'
)

Upvotes: 0

sqluser
sqluser

Reputation: 5672

Use the following query. Also always use explicit JOIN

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

Upvotes: 0

Related Questions