list of Employee having 2 products

Question

Can someone help here.

I have table employee. I want to find out list of employees having 2 products(P1,P2).

Emp_id    Prd_id
  E1         P1
  E1         P2
  E2         P1
  E2         P2
  E2         P3
  E3         P1
  E3         P3
  E4         P1
  E4         P2

So, I want output as

Emp_id
  E1
  E4

Okay, so what problem are you facing? Do you know how to count in SQL? Do you know how to count per employee? What is stopping you to write the query? — Thorsten Kettner, Sep 08 '21 at 20:54
I can see a possible solution using string aggregation here - `xmlagg` would seem to be Teradata's equivalent. — Stu, Sep 08 '21 at 21:22

score 1 · Answer 1 · answered Sep 08 '21 at 21:51

SELECT emp_id
FROM employee
GROUP BY emp_id
HAVING COUNT(CASE WHEN prd_id 'p1' THEN 1 END) > 0 -- p1 exists
   AND COUNT(CASE WHEN prd_id 'p2' THEN 1 END) > 0 -- p2 exists
   AND COUNT(CASE WHEN prd_id NOT IN ('p1', 'p2') THEN 1 END) = 0 -- but not other

Easier to extend to a larger number of products:

SELECT emp_id
FROM 
 ( -- get a unique list first
   SELECT DISTINCT emp_id, prd_id
   FROM employee
 ) AS dt
GROUP BY emp_id
HAVING SUM(CASE WHEN prd_id IN ('p1', 'p2') THEN 1 ELSE -1 END) = 2

score 0 · Answer 2 · answered Sep 08 '21 at 20:54

0

If the requirement is to find employees with any two products, you could use a having condition to count them:

SELECT   emp_id
FROM     employees
GROUP BY emp_id
HAVING   COUNT(*) = 2

If those products must be p1 and p2, you could add another condition on that:

SELECT   emp_id
FROM     employees
GROUP BY emp_id
HAVING   COUNT(*) = 2 AND
         COUNT(CASE WHEN prd_id IN ('p1', 'p2') THEN 1 END) = 2

answered Sep 08 '21 at 20:54

Mureinik

297,002
52
306
350

1

Thanks Mureinik... I was exactly looking for 2nd scenario – Santosh Suplepatil Sep 08 '21 at 21:18
If the table doesn't contain duplicate rows for this (subset of) columns. More reliable way would be to do conditional aggregation per value and check if each of them is `> 0` – astentx Sep 08 '21 at 21:40

list of Employee having 2 products

2 Answers2