CREATE TABLE MainTable(Reference varchar(30),JobType varchar(30) )
INSERT INTO MainTable SELECT 'BS12345','TV Install' UNION ALL SELECT 'BS54321','WIFI Install'
CREATE TABLE RevisitTable(Reference varchar(30),JobType varchar(30))
INSERT INTO RevisitTable SELECT 'BS12345R1','RVI' UNION ALL SELECT 'BS12345R2','RVI'

SELECT * FROM MainTable
LEFT OUTER JOIN RevisitTable
ON CHARINDEX(MainTable.Reference,RevisitTable.Reference ) > 0

/*
Reference	JobType	Reference	JobType
BS12345	TV Install	BS12345R1	RVI
BS12345	TV Install	BS12345R2	RVI
BS54321	WIFI Install	NULL	NULL
*/

SELECT fld1, fld2, fld3
FROM VISITS AS V
        LEFT JOIN REVISITS AS R
                ON V.Reference = LEFT(R.Reference, CHARINDEX('R', R.Reference) - 1)