lots of ways in doing this--using APPLYselect ID, code, go, back from tableA Across apply( select 1 AS Occ from tableA with (nolock) where A.ID = ID and code = 'Z')B--using window functionsselect ID, code, go, back from (select *, SUM(CASE WHEN code = 'Z' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS Cnt FROM tableA )Awhere Cnt > 0--using joinselect A.ID, code, go, back from tableA AINNER JOIN (SELECT DISTINCT ID FROM Table WHERE code = 'Z' )BON B.ID = A.ID--Using IN operatorselect ID,code,go,back from tableA where ID IN ( select IDfrom tableA with (nolock)where code='Z')
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs