SQL Server Select Query for company that has employee role (Owner and CEO) at the same time

userqD5m1 0
Viewed 40

My Table Structure is shown below:

enter image description here

I would like to retrieve company names and person names that working for this company and their position are (CEO and Owner), it means that person must be both (CEO and owner). I've tried so far and created below query, I can select or filter only CEO or Owner.

SELECT cp.id as cp_id,
com.name,
per.name,
pos.position_name

FROM company_person as cp 

INNER JOIN company as com
ON com.id = cp.company_id

INNER JOIN person as per
ON per.id = cp.person_id

INNER JOIN position as pos
ON pos.id = cp.position_id
relational-division tsql sql-server sql
1 Answers

This is a classic Relational Division With Remainder question.

There are a number of solutions. Here is a standard one.

SELECT
  com.name,
  per.name
FROM company as com
CROSS APPLY (
    SELECT
      cp.person_id
    FROM company_person as cp
    INNER JOIN position as pos ON pos.id = cp.position_id
    WHERE com.id = cp.company_id
      AND pos.position_name IN ('CEO', 'Owner')
    GROUP BY
      cp.person_id
    HAVING COUNT(*) = 2
) as cp
INNER JOIN person as per ON per.id = cp.person_id;

If your list of positions is more dynamic, you can put them in a table variable or TVP, then do the following

DECLARE @input TABLE (position_name varchar(50) PRIMARY KEY);
INSERT @input ....

SELECT
  com.name,
  per.name
FROM company as com
CROSS APPLY (
    SELECT
      cp.person_id
    FROM company_person as cp
    INNER JOIN position as pos ON pos.id = cp.position_id
    INNER JOIN @input as i ON i.position_name = pos.position_name
    WHERE com.id = cp.company_id
    GROUP BY
      cp.person_id
    HAVING COUNT(*) = (SELECT COUNT(*) FROM @input)
) as cp
INNER JOIN person as per ON per.id = cp.person_id;

There are other solutions also, such as a double NOT EXISTS

SELECT
  com.name,
  per.name
FROM company as com
INNER JOIN person as per
    ON NOT EXISTS (SELECT 1
        FROM position as pos 
        INNER JOIN @input as i ON i.position_name = pos.position_name
        WHERE NOT EXISTS (SELECT 1
            FROM company_person as cp
            WHERE cp.position_id = pos.id
              AND cp.company_id = com.id
              AND cp.person_id = per.id
        )
    );

Another option is a LEFT JOIN then check that they were all matched

SELECT
  com.name,
  per.name
FROM company as com
CROSS APPLY (
    SELECT
      p.id,
      p.name
    FROM person as per
    CROSS JOIN position as pos
    INNER JOIN @input as i ON i.position_name = pos.position_name
    LEFT JOIN company_person as cp ON cp.position_id = pos.id AND cp.company_id = com.id AND per.id = cp.person_id
    GROUP BY
      p.id,
      p.name
    HAVING COUNT(*) = COUNT(cp.person_id)
) as per;
Charlieface0
Related