Finding rows that do not exist in two tables...

[Ajb2528]

Hi!

I'm tryng to find rows in one table (tblRemainingFiles) that do not match with an entry in another table (tblErrorLog)...

So far, I have the following sql written -

SELECT c.SourcePath, c.ErrorMessage
FROM dbo.tblErrorLog AS c
JOIN dbo.tblRemainingFiles AS e
ON e.Path <> c.SourcePath
WHERE NOT EXISTS
(SELECT *
FROM dbo.tblRemainingFiles AS rf
JOIN dbo.tblErrorLog AS el
ON rf.Path = el.SourcePath
WHERE el.ErrorMessage LIKE '%File Exists in target location%')

but it does not return any rows (I expected at least a few hundred) as the row counts are 19583 for tblErrorLog and 25419 for tblRemainingFiles...

Any help will be appreciated!

Regards,

Alan

 

[bborissov]

Did this query returns ALL rows from tblErrorLog ?

SELECT *
   FROM dbo.tblRemainingFiles AS rf
   JOIN dbo.tblErrorLog AS el
        ON rf.Path = el.SourcePath
   WHERE el.ErrorMessage LIKE '%File Exists in target location%')

If so, the you just have duplicate records in tblRemainingFiles with different ErrorMessage text.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Ajb2528]

Hi bborissov,

Thanks for the reply! No the query you supplied returns 19145 rows out of 19583....

Regards,

Alan

 

[bborissov]

Sorry could you provide some simple data and desired result from it. Today is hard to think for me

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Your join condition is very strange. I would join on the common field instead of trying to join on A.Path <> B.Path.

If you would use a left join and add a condition for the right table's PK to be NULL you'll get all non-matching records, e.g.

select a.* from A left join B on A.Pk = B.PK where B.PK IS NULL

will give you all records that don't have a matching entry in B table.