SQL - Join within the same table for matching fields

Question

I have a table that contains 6 fields

(JobGUID, FunctionName,InFileName,InFileSize,OutFileName,OutFileSize)

One FunctionName is CheckFile
, and the other is UnZipFile
. The

CheckFile

.

InFileName

and

InFileSize

should be equal to the

UnZipFile

.

OutFileName

and

OutFileSize

. I want to do a JOIN that allows me to see each side-by-side so I can compare that file sizes match.

Here is what I have so far. It seems to work, but duplicates each filename and filesize. There are 22 corresponding rows for CheckFile, and 22 rows for UnZipFile. I want it to output only 22 rows, but it outputs 44.

SELECT CheckFile.InFileName, CheckFile.InFileSize, 

       UnZipFile.OutFileName, UnZipFile.OutFileSize, 

       CheckFile.InFileSize - UnZipFile.OutFileSize as 'FileSizeDifference'

FROM [MY_DATABASE].[dbo].[MY_TABLE] CheckFile

JOIN [MY_DATABASE].[dbo].[MY_TABLE] UnZipFile ON CheckFile.InFileName = UnzipFile.OutFileName

WHERE CheckFile.JobGUID = 'Some GUID #' 

  and CheckFile.FunctionName = 'CheckFile'

  and UnZipFile.FunctionName = 'UnZipFile'

  and CheckFile.InFileName like '%.txt'

Answer 1

Try

select distinct etc.

It should do the trick.

SQL - Join within the same table for matching fields

Sign up