Loading

USER WHO HAS TWO ACCOUNTS

USER WHO HAS TWO ACCOUNTS

Sai Reddy

Sai Reddy

7y720

Hi,

Please help me to solve it .

USERID	ACID
100	1
100	2
100	3
100	4
101	2
102	1
103	2
103	3
103	4

I need who has accounts both 2 and 3 .

OUTPUT

OUTPUT
100	2
100	3
103	2
103	3

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
Accepted answer
You can try the following query
select * from (
SELECT Distinct #TempAccount.UserId, #TempAccount.ACID
from #TempAccount
where #TempAccount.UserId in (
select UserId from #TempAccount where ACID IN (2,3) GROUP BY UserId HAVING COUNT(ACID) = 2
)
)a where acid in (2, 3)
The output is as shown below
UserId  ACID
100         2
100         3
103         2
103         3
You can find the live demo here. https://rextester.com/LAGX82666
Hope you will find it as helpful & mark it as accepted answer also.
Sreekanth Reddy7y ago
Hi Sai,

Select USERID,ACID, ROW_NUMBER() over(partition by USERID order by USERID ) as rownumber from Employe WHERE ROWNUMBER < = 2 and ACID in (2,3)

If it helps, mark as answer.
Else, come up with your query.
Thank you.
Sai Reddy7y ago
Hi Srikanth, I need who has accounts 2 as well 3 also. IN (2,3) fetches users including who has single account with 2 and 3 also as follows.

100 2

100 3

103 2

103 3

101 2

Where i don't need single account users.
Sreekanth Reddy7y ago
Hi Sai,

select USERID,ACID where ACID IN (2,3)

use "IN" operator for fetching based on multiple values.

If it helps, mark as answer.
Thank you.