Crazy selection help

Hi everyone -

I have two tables, one table (A) that holds a field called location that
has the partial path information of the file

and the second table (B) holds the full path including the filename

I only want to display the records from the partial path (A)
table that do not reside in the second (B) table


Table A =
imagefilename, description, directory, companyname, location
"96.jpg","test","Prog\2006_02","Marey, John","Prog\2006_02\96.jpg"
"274.JPG","disney","Prog\2006_07","Amy Gross","Prog\2006_07\274.JPG"
"570.jpg","sdfsdf","Prog\2007_06","Lof3,"Test3","Prog\2007_06\570.jpg"
"850.JPG","222","Prog\2007_08","Malis, Mark","Prog\2007_08\850.JPG"

Table B =
imagefilename
"d:\webdata\web\uploads\qfimages\Prog\2006_02\96.jp g"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\112.p df"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\127.j pg"

I was thinking about using a cross join with a like condition,
but it fails (go figure)

could someone offer a little help to the query that i need to
perform??

thanks
tonyuse a LEFT OUTER JOIN from A (left table) to B (right table)

the ON condition will match columns (using LIKE or string functions or whatever)

the WHERE clause will test the PK of the right table being NULL (indicating no match found)|||Why don't you post the SQL you have tried so far?|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.location|||DECLARE @.TableA table (imagefilename varchar(100), [description] varchar(100)
, directory varchar(100), companyname varchar(100), location varchar(100))

DECLARE @.TableB table (imagefilename varchar(100))

INSERT INTO @.TableA (imagefilename, description, directory, companyname, location)
SELECT '96.jpg','test','Prog\2006_02','Marey, John','Prog\2006_02\96.jpg' UNION ALL
SELECT '274.JPG','disney','Prog\2006_07','Amy Gross','Prog\2006_07\274.JPG' UNION ALL
SELECT '570.jpg','sdfsdf','Prog\2007_06','Lof3,Test3','Pr og\2007_06\570.jpg' UNION ALL
SELECT '850.JPG','222','Prog\2007_08','Malis, Mark','Prog\2007_08\850.JPG'

INSERT INTO @.TableB(imagefilename)
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_02\96.j pg' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\112. pdf' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\127. jpg'

SELECT 'TableA' AS Source, Location
FROM @.TableA
UNION ALL
SELECT 'TableB' AS Source, imagefilename
FROM @.TableB b
WHERE NOT EXISTS (SELECT * FROM @.TableA a
WHERE b.imagefilename LIKE '%'+a.location)|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.locationnow change it to a LEFT OUTER JOIN and add this --

... where tbl1.location IS NULL|||GREAT !!!!

you folks are the best!

thank you x10000