In query

  • Thread starter Thread starter Chris Kennedy
  • Start date Start date
C

Chris Kennedy

I have two tables with some duplicate filenames in a field. I want to
extract the ones in the tmpfilenames that aren't in the tblWeb table. This
returns nothing when there are definitely filenames in the tmpfilenames
table that arean't in the tblWeb table

SELECT FileName FROM tmpfilenames WHERE FileName not in (select
picturefilename from tblWeb);
 
Chris said:
I have two tables with some duplicate filenames in a field. I want to
extract the ones in the tmpfilenames that aren't in the tblWeb table. This
returns nothing when there are definitely filenames in the tmpfilenames
table that arean't in the tblWeb table

SELECT FileName FROM tmpfilenames WHERE FileName not in (select
picturefilename from tblWeb);
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are you sure the names are spelled the same in both tables? Sometimes
it may appear that the names are the same, but there may be extra
spaces, or hidden characters, at the beginning or end of the name that
causes the equivalency test to fail. IOW: "john" = "john " is false
because the 2nd john has a space after the final n.

You could also rewrite the query like this:

SELECT FileName
FROM tmpFileNames As T LEFT JOIN tblWeb As W
ON T.FileName = W.FileName
WHERE W.FileName IS NULL

This may have the same problems as the first query if the names are
stored as described above.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlxOfYechKqOuFEgEQIcwwCgiczFc9EGikAG+fADy2GivbTPetsAmwcb
0zQB/L8MszNnOWTVtKHe2B1M
=9Ewk
-----END PGP SIGNATURE-----
 
Back
Top