score:0

Find all client/policies that have both types by use of GROUP BY and HAVING. Then use this in your from clause so you get only those client/policies.

select ...
from
(
    select client, policyno 
    from mytable
    where trantype in ('NBS','XLN')
    group by client, policyno 
    having count(distinct trantype) = 2
) wanted
inner join sometable on sometable.client = wanted.client and sometable.policyno = wanted.policyno
...

score:1

You could use EXISTS:

SELECT t1.*
FROM dbo.TableName t1
WHERE t1.TranType IN('NBS','XLN')
AND EXISTS
(
    SELECT 1 FROM dbo.TableName t2
    WHERE t1.Client = t2.Client
      AND t1.PolicyNo = t2.PolicyNo
      AND t1.TranType <> t2.TranType
      AND t2.TranType IN('NBS','XLN')   
 )

More questions

More questions with similar tag