How to fetch records having multiple rows with different column values

Question

I have a table disp table. Which has 3 columns, service_code, row_status and business_unit_id.

service_code	row_status	business_unit_id
A	1	BU1
A	0	BU2
A	0	BU3
B	1	BU1
B	0	BU2
C	0	BU2
C	0	BU3

I need a query that will fetch me service_code which is 100% row_status = 0 across all the business_unit_id. So in the above case the answer should be C. The table that i am working on has millions of records. How can i figure out which service_code has row_status = 0 at every business_unit_id? I have written a query but it seems its returning A, B, C. That is not correct.

create table #tmpTable (pc_ccs_9xx_service_code varchar(10));
create table #tmpTable1 (pc_ccs_9xx_service_code varchar(10));

insert into #tmpTable
select top 500 
pcdis.service_code
from pc_ccs_9xx_deposit_install_service pcdis
where pcdis.row_status = 1
order by pcdis.service_code asc

select * from #tmpTable

insert into #tmpTable1
select top 500 pcdis.service_code from pc_ccs_9xx_deposit_install_service pcdis
join #tmpTable tt on tt.pc_ccs_9xx_service_code = pcdis.service_code and pcdis.row_status = 1
order by pcdis.service_code asc

select top 500 tt.pc_ccs_9xx_service_code from #tmpTable tt
join #tmpTable1 tt1 on --tt1.pc_ccs_9xx_service_code = tt.pc_ccs_9xx_service_code and 
tt1.pc_ccs_9xx_service_code not in (select top 100 pc_ccs_9xx_service_code from #tmpTable)
order by tt.pc_ccs_9xx_service_code asc

DROP table #tmpTable
DROP table #tmpTable1

Answer 1

WITH YOUR_TABLE_DATA (service_code,row_status,business_unit_id)AS
(
  SELECT 'A',   1,  'BU1' UNION ALL
  SELECT 'A' ,  0,  'BU2' UNION ALL
  SELECT 'A' ,  0,  'BU3' UNION ALL
  SELECT 'B',   1,  'BU1' UNION ALL
  SELECT 'B',   0,  'BU2' UNION ALL
  SELECT 'C',   0,  'BU2' UNION ALL
  SELECT 'C' ,  0,  'BU3'
)
SELECT C.service_code
FROM YOUR_TABLE_DATA AS C
GROUP BY C.service_code
HAVING MIN(C.row_status)=MAX(C.row_status)
AND MIN(C.row_status)=0

Please try this query.

Answer 2

You can use NOT EXISTS as follows:

SELECT t.*
  FROM your_table t
WHERE NOT EXISTS
      (SELECT 1 FROM your_table tt 
        WHERE tt.service_code = t.service_code
          AND tt.row_status <> 0);

You can also use GROUP BY and HAVING as follows:

SELECT t.service_code
  FROM your_table t
GROUP BY t.service_code
HAVING COUNT(CASE WHEN t.row_status <> 0 THEN 1 END) = 0;

-- If your row_status contains 0 or positive numbers only 
-- then use following HAVING clause
HAVING SUM(t.row_status) = 0;