I have a requirement which needs to fetch all the distinct records for key which has a particular value for all rows, and not if it has some other value in other rows.

Table structure:

  Select TOP (1000)
   [Old_Branch_Code]
  ,[Computer_Name]
  ,[Migrated]
  ,[Migration_Date]
  FROM [TestDB].[dbo].[ABS_Computer_Details]

So, if the table ABS_Computer_Details contains the records like

  1, 'X', 'SUCCESS','12/12/12'
  1, 'Y', 'SUCCESS','12/12/12'
  1, 'Z', 'SUCCESS','12/12/12'
  1, 'W', 'Failed','12/12/12'
  2, 'Y', 'SUCCESS','12/12/12'
  2, 'Z', 'SUCCESS','12/12/12'
  3, 'Z', 'Failed','12/12/12'
  4, 'Z', 'SUCCESS','12/12/12'

So, it should only select branch_code = 2 and branch_code = 4; because one of the column for branch_code = 1 and branch_code = 3 has status = 'Failed'.

My attempt:

select Old_Branch_Code from dbo.ABS_Computer_Details
group by Old_Branch_Code,Migrated having Migrated='SUCCESS'

But, my query is returning values for branch_code = 1, branch_code = 2 and branch_code = 4. As per my requirement, I should've got branch_code = 2 and branch_code = 4 only.

Off-topic: I know this is very trivial, but I'm poor with DB queries as I'm not a DBA guy.

One approach here is to use conditional aggregation in the HAVING clause which checks for the presence of any migration other than SUCCESS. If we can't find anything other than SUCCESS then we retain that branch code.

SELECT Old_Branch_Code
FROM dbo.ABS_Computer_Details
GROUP BY Old_Branch_Code
HAVING SUM(CASE WHEN migrated != 'SUCCESS' THEN 1 ELSE 0 END) = 0

more