0
\$\begingroup\$

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
\$\endgroup\$
4
  • \$\begingroup\$ I did think try that, but I could reference the alias in my where. So I had to re-do the SUM(... etc. in the where again.Which seemed too much. See the following for a similar issue: stackoverflow.com/questions/715462/… \$\endgroup\$ Jan 16, 2017 at 10:06
  • \$\begingroup\$ ^ couldn't* reference \$\endgroup\$ Jan 16, 2017 at 10:19
  • \$\begingroup\$ Why is there a SUM? Couldn't CASE WHEN SUM(CASE WHEN ep.type IN (1,2) THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END has_screen be simplified to CASE WHEN ep.type IN (1,2) THEN 1 ELSE 0 END has_screen? (Same for has_keyboard.) \$\endgroup\$
    – BCdotWEB
    Jan 16, 2017 at 10:21
  • \$\begingroup\$ Apologies, I think the query was missing a 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\$ Jan 16, 2017 at 10:44

1 Answer 1

1
\$\begingroup\$

BOOL values

Your outer case statements convert a value to itself. SUM(CASE WHEN type IN (1,2) THEN 1 ELSE 0 END) > 0 is TRUE or FALSE, which are equivalent to 1 and 0.

WHERE vs HAVING

WHERE will filter rows from the input, which means that you can't use an aggregate. HAVING filters after the aggregation, which means you can drop the outer query.

Unnecessary JOIN

You are only using the employee_id from employee, which is present in employee_peripheral

Resulting query

SELECT employee_id,
SUM(CASE WHEN type IN (1,2) THEN 1 ELSE 0 END) > 0 AS has_screen,
SUM(CASE WHEN type > 2 THEN 1 ELSE 0 END) > 0 AS has_keyboard
FROM employee_peripheral
GROUP BY employee_id
HAVING (SUM(CASE WHEN type IN (1,2) THEN 1 ELSE 0 END) > 0) <> (SUM(CASE WHEN type > 2 THEN 1 ELSE 0 END) > 0)
\$\endgroup\$
3
  • \$\begingroup\$ 1) I assume the employee can have more than one peripheral in (1,2) and more than one >2 (might be odd in what it would mean, but certainly possible from database perspective), so the SUM(...) > 0 check is necessary. 2) With the subquery in place, the enter thing is more readable to me - the subquery provides analytics with computations and the outer query is an actual search. Of course both versions work and a smart query parser (like SQL Server) optimizes it well. 3) Agree that employee join is indeed unnecessary. \$\endgroup\$
    – Misza
    Jan 16, 2017 at 13:42
  • \$\begingroup\$ @Misza 1) ? I still have the SUM(...) > 0, I just use the result of that expression rather than wrapping it in a CASE. 2) Yes, a subquery or CTE to define has_screen and has_keyboard in one place could be considered more readable \$\endgroup\$
    – Caleth
    Jan 16, 2017 at 14:41
  • \$\begingroup\$ In what SQL dialect can you simply write "SELECT (int expression) > 0 AS has_something"? And what data type this expression supposedly returns? I'm trying in SQL Server and I'm getting syntax error on the ">" sign. \$\endgroup\$
    – Misza
    Jan 17, 2017 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.