RE: Querying Results From a Query

Lowell

SSC Guru

Points: 323504

December 4, 2012 at 10:52 am

#1565434

we'd have to see the actual table structure and sample data (CREATE TABLE ...INSERT INTO)

but you could do something as easy as joining with an "exotic" join...that is not with an equals sign:

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

/*

ReferenceJobTypeReferenceJobType

BS12345TV InstallBS12345R1RVI

BS12345TV InstallBS12345R2RVI

BS54321WIFI InstallNULLNULL

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!