I have an
employee table, employees may have multiple peripherals. I want to bring back all employees that have a peripheral of type 1 or 2, but that don't have any others, and vice versa (i.e. have a peripheral of type other than 1 or 2 but not have any peripherals of 1 or 2). For each of these employees, there should be a flag to show which of the two categories/product types they have.
This query works, but feels a bit verbose:
SELECT employee_id, has_screen, has_keyboard FROM ( SELECT e.employee_id, CASE WHEN SUM(CASE WHEN ep.type IN (1,2) THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END has_screen, CASE WHEN SUM(CASE WHEN ep.type > 2 THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END has_keyboard FROM employee e JOIN employee_peripheral ep ON e.employee_id = ep.employee_id GROUP BY e.employee_id ) T WHERE has_screen <> has_keyboard