SQLize Online / PHPize Online  /  SQLtest Online

create table #database ( NRIC varchar(50) ,OTHER_ID varchar(50) ,CLIENT_ID varchar(50) ) insert into #database values ('123456015678','a555555','1234') insert into #database values ('O32242','997654-06-9382','5678') insert into #database values ('990424-09-8282','KSD2323','986') insert into #database values ('987654-02-5987','K02333','1323') insert into #database values ('NA','930233043333','404345') insert into #database values ('90203043214','KJ82932E','945') insert into #database values ('760423068777','NA','5454') insert into #database values ('631123048754','NA','34934') insert into #database values ('NA','650203099282','3535') insert into #database values ('981312-03-9242','A923943','45545') insert into #database values ('NA','650212-09-5444','9897') insert into #database values ('NA','NA','5433') insert into #database values ('K1203343','NA','3545') insert into #database values ('907865','BJ823932','45436') insert into #database values ('991216-04-0987','EF65342','7897') insert into #database values ('111213096574','19132','9685') insert into #database values ('991231-05-43333','A290232','34662') insert into #database values ('990504-02-4343','NA','643264') insert into #database values ('990504-02-4343','B11111','643264') insert into #database values ('995432-06-1234','H88322','32564') insert into #database values ('NA','NA','64564') insert into #database values ('NA','WF2323','36346') create table #fraudlisting ( NRIC varchar(50) ,OTHER_ID varchar(50) ) insert into #fraudlisting values ('123456-01-5678','a555555') insert into #fraudlisting values ('987654-02-5987','NA') insert into #fraudlisting values ('930233043333','NA') insert into #fraudlisting values ('090203-04-3214','KJ82932E') insert into #fraudlisting values ('NA','650203-09-9282') insert into #fraudlisting values ('NA','A923943') insert into #fraudlisting values ('907865','BJ823932') insert into #fraudlisting values ('EF65342','NA') insert into #fraudlisting values ('111213-09-6574','NA') insert into #fraudlisting values ('990504024343','NA') insert into #fraudlisting values ('995432061234','NA') create table #results ( NRIC varchar(50) ,OTHER_ID varchar(50) ,CLIENT_ID varchar(50) ) insert into #results values ('123456-01-5678','a555555','1234') insert into #results values ('987654-02-5987','K02333','1323') insert into #results values ('930233043333','NA','404345') insert into #results values ('090203-04-3214','KJ82932E','945') insert into #results values ('650203-09-9282','NA','3535') insert into #results values ('981312-03-9242','A923943','45545') insert into #results values ('907865','BJ823932','45436') insert into #results values ('991216-04-0987','EF65342','7897') insert into #results values ('111213-09-6574','19132','9685') insert into #results values ('990504024343','B11111','643264') insert into #results values ('995432061234','H88322','32564') SELECT d.* FROM ( SELECT C.CLIENT_ID, [NRIC] = isnull(n.nric, ''), [OTHER_ID] = isnull(o.other_id, '') FROM ( SELECT DISTINCT client_id FROM #database ) c LEFT JOIN ( SELECT DISTINCT CLIENT_ID, [NRIC] = CASE WHEN nric LIKE '%-%' OR len(nric) > 10 OR nric = '907865' THEN nric WHEN other_id LIKE '%-%' OR len(other_id) > 10 THEN other_id END FROM #database ) n ON c.client_id = n.client_id LEFT JOIN ( SELECT CLIENT_ID, [OTHER_ID] = nullif(( replace(( CASE WHEN len(other_id) < 10 AND other_id <> 'na' THEN other_id WHEN len(nric) < 10 AND other_id <> 'na' AND nric <> '907865' THEN nric END ), 'NA', '') ), '') FROM #database WHERE nullif(( replace(( CASE WHEN len(other_id) < 10 AND other_id <> 'na' THEN other_id WHEN len(nric) < 10 AND other_id <> 'na' AND nric <> '907865' THEN nric END ), 'NA', '') ), '') IS NOT NULL ) o ON c.client_id = o.client_id ) d LEFT JOIN ( SELECT DISTINCT [nric] = CASE WHEN nric = '907865' OR ( len(nric) > 10 AND nric <> 'na' ) THEN nric WHEN other_id = '907865' OR ( len(other_id) > 10 AND other_id <> 'na' ) THEN other_id END FROM #fraudlisting ) n ON replace(d.nric, '-', '') = replace(n.nric, '-', '') LEFT JOIN ( SELECT DISTINCT [other_id] = CASE WHEN nric <> '907865' AND len(nric) < 10 AND nric <> 'na' THEN nric WHEN other_id <> '907865' AND len(other_id) < 10 AND other_id <> 'na' THEN other_id END FROM #fraudlisting ) o ON d.other_id = o.other_id WHERE n.nric IS NOT NULL OR o.other_id IS NOT NULL ORDER BY d.client_id