chrisdoubleu13 chrisdoubleu13 - 4 months ago 10
SQL Question

SQL - Join within the same table for matching fields

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

Try

select distinct etc.

It should do the trick.

Comments