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
where
. So I had to re-do theSUM(...
etc. in thewhere
again.Which seemed too much. See the following for a similar issue: stackoverflow.com/questions/715462/… \$\endgroup\$SUM
? Couldn'tCASE WHEN SUM(CASE WHEN ep.type IN (1,2) THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END has_screen
be simplified toCASE WHEN ep.type IN (1,2) THEN 1 ELSE 0 END has_screen
? (Same forhas_keyboard
.) \$\endgroup\$GROUP BY
. But, basically, @BCdotWEB - I need to say "there exists any peripheral of type 1 or 2 for each employee". Not, "for each record in the join is it type 1 or 2". I need 1 row per employee. I think my lack of GROUP BY probably threw you off. \$\endgroup\$