phpmyadmin Question posted in
The official documentation can be found here.

want persons that are in all four deparments – Phpmyadmin

The problem is when I run the code it checks to see if any person has been in department 1 then stops but only want persons that have been in all four departments

SELECT
    p.person_id AS ID,
    CONCAT(p.firstname, " ", p.surname) AS 'Employee Name'
FROM
    person AS p, 
    allocation_to_department AS ad
WHERE
    ad.person_id = p.person_id
    AND ad.department_id= ('1,2,3,4')
Tags:

3

Answers


  1. 0

    You can do it by grouping the joined tables and select only the rows that have all 4 department_ids:

    SELECT
  p.person_id AS ID,
  CONCAT(p.firstname, ' ', p.surname) AS `Employee Name`
FROM person AS p inner join allocation_to_department AS ad
on ad.person_id = p.person_id 
where ad.department_id in (1, 2, 3, 4)
group by ID, `Employee Name`
having count(*) = 4
    Login or Signup to reply.
  2. 0

    Here is an aggregate query that will return the persons that belong to all 4 departments (id 1 to 4):

    SELECT
    p.person_id AS ID,
    CONCAT(p.firstname, " ", p.surname) AS 'Employee Name'
FROM
     person AS p
     INNER JOIN allocation_to_department AS ad
         ON ad.person_id = p.person_id          
GROUP BY 
    p.person_id, 
    p.first_name, 
    p.surname
HAVING 
    MAX(ad.department_id = 1) = 1
    MAX(ad.department_id = 2) = 1
    MAX(ad.department_id = 3) = 1
    MAX(ad.department_id = 4) = 1

    Another solution would to be use an IN clause and COUNT(DISTINCT ...):

    SELECT
    p.person_id AS ID,
    CONCAT(p.firstname, " ", p.surname) AS 'Employee Name'
FROM
     person AS p
     INNER JOIN allocation_to_department AS ad
         ON ad.person_id = p.person_id
         AND ad.department_id IN (1, 2, 3, 4)
GROUP BY 
    p.person_id, 
    p.first_name, 
    p.surname
HAVING
    COUNT(DISTINCT ad.department_id) = 4

    NB: always use explicit JOINs instead of old-school, implicit joins. I modified the queries accordingly.

    Login or Signup to reply.
  3. 0

    Try this…

    SELECT p.person_id AS ID, CONCAT(p.firstname, " ", p.surname) AS 'Employee Name'
FROM person AS p, allocation_to_department AS ad
WHERE
ad.person_id = p.person_id 
AND
ad.department_id in (1,2,3,4)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search