How can I select LINK_USER_ID who has only 1 CA account and 1 SA account. There are users which have 2 CA and 1 SA or 1 CA only or 1 SA only using SQL query.

NOTE: 1 LINK_USER_ID can linked to multi LINK_CIS_NO.

Sample data

Expected result

CodePudding user response：

Aggregate by id and count conditionally:

select link_user_id
from mytable
group by link_user_id
having count(case when link_acct_type = 'CA' then 1 end) = 1
   and count(case when link_acct_type = 'SA' then 1 end) = 1`.

CodePudding user response：

select LINK_USER_ID
from table
where LINK_ACCT_TYPE in ('CA', 'SA')
group by LINK_USER_ID
having count(distinct LINK_ACCT_TYPE) = 2

The query groups the data by LINK_USER_ID and then checks to see if there are two distinct values for LINK_ACCT_TYPE (CA and SA). If there are, then that LINK_USER_ID is included in the results.