Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    91

    Unanswered: 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","P rog\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.j pg"
    "d:\webdata\web\uploads\qfimages\Prog\2006_03\112. pdf"
    "d:\webdata\web\uploads\qfimages\Prog\2006_03\127. jpg"

    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
    tony

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use 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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you post the SQL you have tried so far?
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2004
    Posts
    91

    here ya go

    SELECT *
    FROM tbl2 INNER JOIN
    tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.location

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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','Prog\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.jpg' 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)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alt-088
    SELECT *
    FROM tbl2 INNER JOIN
    tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.location
    now change it to a LEFT OUTER JOIN and add this --

    ... where tbl1.location IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2004
    Posts
    91
    GREAT !!!!

    you folks are the best!

    thank you x10000

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •