Try this....

Select Distinct ENCOUNTER_KEY from #ENCOUNTER_PARTY where ENCOUNTER_KEY not in

(Select ENCOUNTER_KEY from #ENCOUNTER_PARTY where PARTY_ROLE_TYPE_KEY = 2)

Regards,

Carthik

i cannot change the design in the sense, other application are using the same database and which will store the data in a comma separated format.

In the join if we...

Thank you for your suggestions. The database in designed in such a way that it has to store comma separated values and this can not be changed now.

Obviously it will be different....

Suppose

T1 has data ( 1, 2,3,4,5)

T2 has data( 2,3,5)

In the join condition which you wrote, it will check like

1 <> 2 -> true display 1

2<>...