CODEWITHSUNDEEP
sqlsql-serverjoin
danglesauce19
danglesauce19

Reputation: 133

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'

Upvotes: 0

Views: 43

Answers (2)

brou
brou

Reputation: 21

You need to use INNER JOIN

SELECT CheckFile.InFileName, CheckFile.InFileSize,
       UnZipFile.OutFileName, UnZipFile.OutFileSize,
       CheckFile.InFileSize - UnZipFile.OutFileSize as 'FileSizeDifference'
FROM [MY_DATABASE].[dbo].[MY_TABLE] CheckFile
**INNER 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'

As per : w3schools

INNER JOIN: Returns all rows when there is at least one match in BOTH tables

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows when there is a match in ONE of the tables

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Try 

select distinct etc.

It should do the trick.

Upvotes: 1

Related Questions