Hi! Could we please enable some services and cookies to improve your experience and our website?

SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular

SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code. You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.

Copy Format Clear
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

Stuck with a problem? Got Error? Ask ChatGPT!

Copy Clear