Pull all rows with policy number X where at least one row has trans code of Y using TSQL

Question

I have a table with rows policy_nbr and trans_cd. There are multiple trans_cd's per policy (even of the same type). I would like to pull all policy records where said policy has a trans_cd of 'ER' or 'RO'. So any policy_nbr that has a trans_cd of 'ER' or 'RO' associated with it I want to pull all of the entries with that policy_nbr into some new temp table, not just the ones with 'ER' or 'RO'.

The code I have right now is this:

SELECT * FROM urs_prem_detail_interim
INTO ER_RO_urs_prem_detail
WHERE uws_trans_cd IN ('ER','RO')

But I know this won't really work. For instance, if I have policy_nbr '123' and there is an entry of this policy number with trans_cd of 'ER' then I want to pull all values with policy_nbr 123, not just the one with the 'ER' like my current code is doing.

Any help is much appreciated! I apologize if my explanation is confusing.

Answer 1

Using sub-query: The inner select query will pull in all the policy_nbr for trans_cd as 'ER' or 'RO' and then the outer query will pull all the records with matching policy_nbr.

select *
  from urs_prem_detail_interim
 where policy_nbr in (select distinct policy_nbr
                        from urs_prem_detail_interim
                       where uws_trans_cd in ('ER', 'RO'))