Retrieve items belonging to several distinct sub categories using SQL

Question

Imagine I have inventory items that fall into 2 main divisions Engineering (Eng) and Information Technology (IT). In addition, these items are further group into several sub-categories e.g., for IT department I have items in sub-categories IT059, IT059L.. etc and similarly items in sub-categories Eng200, Eng209..etc for Eng. I need to retrieve only those items that are found in both IT and Eng subcategories. The 2 database tables are Item and Department.

Item table

item_id,    item_name,      date_purchased
101,        A101,           2012-01-01
101,        A101,           2012-01-02
101,        A101,           2012-01-03
101,        A101,           2012-01-04
150,        B150,           2012-01-05
202,        C202,           2012-01-06
202,        C202,           2012-01-07
202,        C202,           2012-01-08
221,        D221,           2012-01-09
303,        E303,           2012-01-10
303,        E303,           2012-01-11
303,        E303,           2012-01-12
351,        F351,           2012-01-13
404,        G404,           2012-01-14
404,        G404,           2012-01-15
414,        H414,           2012-01-16
505,        I505,           2012-01-17
505,        I505,           2012-01-18
516,        J516,           2012-01-19
606,        K606,           2012-01-20
606,        K606,           2012-01-21
606,        K606,           2012-01-22
707,        L707,           2012-01-23
707,        L707,           2012-01-24
707,        L707,           2012-01-25
707,        L707,           2012-01-26
707,        L707,           2012-01-27
707,        L707,           2012-01-28
707,        L707,           2012-01-29
808,        M808,           2012-01-30
808,        M808,           2012-01-31
808,        M808,           2012-02-01
808,        M808,           2012-02-02
909,        N909,           2012-02-03
909,        N909,           2012-02-04
909,        N909,           2012-02-05
909,        N909,           2012-02-05
909,        N909,           2012-02-07
950,        O950,           2012-02-08
950,        O950,           2012-02-09
970,        P970,           2012-02-10
970,        P970,           2012-02-11
970,        P970,           2012-02-12
970,        P970,           2012-02-13
970,        P970,           2012-02-14
999,        Q999,           2012-02-15

Department table

item_id,    department_id
101,        Eng200
101,        IT059
101,        IT059L
101,        IT069
150,        Eng200
202,        Eng209
202,        IT059
202,        IT060L
221,        IT060L
303,        Eng209
303,        IT06.9
303,        IT069
351,        Eng20-
404,        Eng209
404,        IT064
414,        Eng20.1
505,        Eng209
505,        IT060
516,        IT05..
606,        Eng209
606,        IT060M
606,        IT069
707,        Eng209
707,        IT058H
707,        IT059
707,        IT06.0
707,        IT06.9
707,        IT060
707,        IT069
808,        Eng209
808,        IT059
808,        IT059M
808,        IT069
909,        Eng209
909,        IT059
909,        IT060
909,        IT060M
909,        IT069G
950,        Eng20-
950,        IT069
970,        Eng20-
970,        IT058L
970,        IT059
970,        IT059L
970,        IT069
999,        Eng20?

If I had only 2 department_ids (IT and Eng), I could write a query similar to:

select max(i.item_id)
from item i
join department d
on i.item_id = d.item_id
group by i.item_id
having count(distinct d.department_id) > 1

However in my case I have several department_ids that need to be checked first to determine if the item fulfills the condition of belonging to both IT and Eng categories.

Expected output

item_id
101
202
303
404
505
606
707
808
909
950
970

How can I retrieve the desired output?

Answer 1

You could use CROSS APPLY to pull out the prefix, then use COUNT(DISTINCT Prefix.

Note that your Department table should really be called DepartmentItem as it is not a unique list of Department.

SELECT
  i.item_id
FROM Item i
JOIN DepartmentItem di ON di.item_id = i.item_id
CROSS APPLY (VALUES(
    CASE WHEN di.department_id LIKE 'IT%' THEN 'IT'
         WHEN di.department_id LIKE 'Eng%' THEN 'Eng'
    END
)) v(Prefix)
WHERE (di.department_id LIKE 'IT%' OR di.department_id LIKE 'Eng%')
GROUP BY
  i.item_id
HAVING COUNT(DISTINCT v.Prefix) = 2;

Note also that I haven't used the v.Prefix value in the WHERE, as you wouldn't be able to use indexes on that.

db<>fiddle

Ideally, you would have a separate Department table, which had a DepartmentType column, then you could just do

SELECT
  i.item_id
FROM Item i
JOIN DepartmentItem di ON di.item_id = i.item_id
JOIN Department d ON d.department_id = di.department_id
WHERE di.Type IN ('IT', 'Eng')
GROUP BY
  i.item_id
HAVING COUNT(DISTINCT di.Type) = 2;