*************

SQL code: 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

SQL version:

Stuck with a problem? Need help? Ask professionals on our Telegram channel!

Join the channel

Your quersion is posted.
Join our Telegram channel and get answers.

SQLize Online / PHPize Online

A A A

Donate Popular

Donate

Popular

SQLize Online / PHPize Online A A A

Donate Popular Donate Popular

SQLize Online / PHPize Online

A A A

Donate Popular

Donate

Popular