-- Set Up data
DECLARE @tblCodes TABLE
(
	CodeID INT, 
	RegionalID INT, 
	ExtName VARCHAR(100), 
	SubsNDCD VARCHAR(4), 
	LocCD VARCHAR(4),
	UpdateStatus INT,
	RegionDesc VARCHAR(100)
)

INSERT @tblCodes
SELECT 5485, 4, 'BENMORE GARDENS', '011', '523', 0, 'GAUTENG CENTRAL REGION' UNION ALL
SELECT 5486, 4, 'EASTLEIGH', '011', '524', 0, 'GAUTENG CENTRAL REGION' UNION ALL
SELECT 5487, 4, 'NEW DOORNFONTEIN', '011', '525', 0, 'GAUTENG CENTRAL REGION' UNION ALL
SELECT 5488, 4, 'ROSEBANK', '011', '526', 0, 'GAUTENG CENTRAL REGION' UNION ALL
SELECT 5489, 4, 'MOROKA', '011', '527', 0, 'GAUTENG CENTRAL REGION' UNION ALL
SELECT 5490, 4, 'DIEPKLOOF', '011', '655', 0, 'GAUTENG CENTRAL REGION' UNION ALL
SELECT 7973, 1, 'PRETORIA', '011', '3011', 0, 'GAUTENG CENTRAL REGION' UNION ALL
SELECT 7974, 1, 'HARTEBEESHOEK', '011', '3012', 0, 'GAUTENG CENTRAL REGION'


DECLARE @tblDongleArea TABLE
(
	RegionalID INT, 
	CallerID VARCHAR(10), 
	RegionalDialup VARCHAR(10),
	DongleAreaCode VARCHAR(2)
)

INSERT @tblDongleArea
SELECT 4, '0116559000', '0800005027', 'MZ' UNION ALL
SELECT 4, '0116559000', '0800005027', 'MD' UNION ALL
SELECT 4, '0116559000', '0800005027', 'ME' UNION ALL
SELECT 4, '0116559000', '0800005027', 'UL' UNION ALL
SELECT 4, '0116559000', '0800005027', 'MA' UNION ALL
SELECT 4, '0116559000', '0800005027', 'MB' UNION ALL
SELECT 4, '0116559000', '0800005027', 'MC'

DECLARE @tblDongleAreaCode TABLE 
(
	AreaID INT,
	RegionalID INT,
	DongleAreaCode VARCHAR(2),
	AreaDesc VARCHAR(50),
	UpdateStatus INT
)

INSERT @tblDongleAreaCode
SELECT 1, 6, 'AA', 'Parrow', 0 UNION ALL
SELECT 2, 6, 'AB', 'Malmesbury', 0 UNION ALL
SELECT 3, 6, 'AC', 'Worcester', 0 UNION ALL
SELECT 4, 6, 'AD', 'Paarl', 0 UNION ALL
SELECT 5, 6, 'AE', 'Somerset West', 0 UNION ALL
SELECT 6, 4, 'MA', 'Bloemfontein', 0 UNION ALL
SELECT 7, 4, 'MB', 'Welkom', 0 UNION ALL
SELECT 8, 4, 'MC', 'Kroonstad', 0

-- I provided two queries so you can try 
-- and see which performs better on your actual data.

-- Query 1
SELECT
	dac.AreaID, 
	dac.RegionalID,
	dac.DongleAreaCode,
	dac.AreaDesc,
	dac.UpdateStatus
FROM 
	@tblCodes c
INNER JOIN
	@tblDongleArea da
	ON c.RegionalID = da.RegionalID
INNER JOIN
	@tblDongleAreaCode dac
	ON da.DongleAreaCode = dac.DongleAreaCode
WHERE
	CHARINDEX(c.SubsNDCD + c.LocCD, da.CallerID) > 0

-- Query 2
SELECT
	dac.AreaID, 
	dac.RegionalID,
	dac.DongleAreaCode,
	dac.AreaDesc,
	dac.UpdateStatus
FROM 
	@tblCodes c
INNER JOIN
	@tblDongleArea da
	ON CHARINDEX(c.SubsNDCD + c.LocCD, da.CallerID) > 0
INNER JOIN
	@tblDongleAreaCode dac
	ON da.DongleAreaCode = dac.DongleAreaCode