CODEWITHSUNDEEP
mysqljoinself
pratik
pratik

Reputation: 185

Fetch values for different rows in mysql

I am using MySql and please refer http://postimg.org/image/waso0lsnf/ to see my table structure. I want to fetch the department_id which enduser_to_department = Y as well as department_to_enduser = Y. Value may be available in different row for the same department_id.

I have tried following way but it's working for the single row.

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

http://postimg.org/image/waso0lsnf/

Edit : added image from URL. enter image description here

Upvotes: 1

Views: 57

Answers (3)

SanG
SanG

Reputation: 57

Hope this will solve ur problem

SELECT departments.department_id, departments.department FROM custom_forms_departments left join 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') ORDER BY departments.department_id DESC

Upvotes: 0

SanG
SanG

Reputation: 57

in where clause you can specify condition

enduser_to_departmen = 'Y' and department_to_enduser = 'Y'

instead of group by u can use distinct

Upvotes: 0

Nighthunter22
Nighthunter22

Reputation: 273

Try this:

    SELECT `department_id`
    FROM   `custom_forms_departments` 
    WHERE  `enduser_to_department` = 'Y'
    AND    `department_to_enduser` = 'Y'
    GROUP BY `departments`.`department_id` 
    ORDER BY departments.department_id DESC

Edit: No join now.

I just changed OR for AND since you want them both to be 'Y', and changed the join.

Upvotes: 1

Related Questions