Nevermind my question... just realized there was something else wrong with my class. Thanks

@Chris Stewart 
THANK YOU. This worked!

@Chris Stewart 
I have updated my post and hopefully this gives some more clarity. 

I did try your suggestion with a few minor adjustments to the ones in bold but it did not pull anything unfortunately. 

SELECT *                      
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI' 
AND ProvId =(
             Select ProvId 
             from PhysTable 
             where ProviderName = 'DOE,JOE' 
              AND Type='NPI' 
              and IdentityId = '345678'
            )

@David Hockenbroch you are correct. Its quite frustrating as I want that second row. 

 

@David Hockenbroch 
I realize why I confused you, sorry. So YES we do get the IdentityId of "345678" of Type NUM but we do not get the IdentityId of "12345" or "345678" of Type NPI. 

@David Hockenbroch 
When I run 

SELECT IdentityId                       
FROM PhysTable                        
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI'    

I get two rows

@David Hockenbroch Everything else correct but one correction - what I want is the IdentityId

@David Hockenbroch I tried this before and it doesn't pull anything unfortunately 

@Chris Stewart I initially marked this as the correct answer but I just realized that was incorrect. We do not get the ProvId "8252" in the message, we do get the IdentityId "345678"

Thank you Julius

Thanks Chris 

Thank you @Robert Cemper 

@Herman Slagman this worked. However, when I had more than one repeating values e.g. 

Set x="Purple,Black,Yellow,Pink,Orange,White,a,b,c,d,e,f"
Set y="Red,Green,Orange,Yellow,z,y,x,w,v"

It only returned that Yellow was found in both lists and ignored the Orange. I probably just need to edit the code that you shared. Thank you!

Thank you @David Hockenbroch ,  I will try this out 

It does, thank you @Roger Merchberger 

@Eduard Lebedyuk @Nick Petrocelli one last question, what if I wanted the output to be "Cat so only one " ?
 

Thank you @Eduard Lebedyuk and @Nick Petrocelli 
 

Thank you @Prasanth Annamreddy. That worked! 

Thank you @Enrico Parisi @Kurro Lopez @Ambrogio De Lorenzo 

 

Will do, thanks!