I have two tables, each table needs

  • I have two tables, each table needs

    columns to be combined to create a path (location on hard drive)

    first table combines into a field called file1

    second table combines into a field called file2

    SELECT DatabaseImagesList.Path + DatabaseImagesList.Directory + '\' + DatabaseImagesList.FileName AS file1

    SELECT DiskImagesList.Folder + DiskImagesList.DiskFilename AS file2

    I need to do a join between databaseimageslist and diskimagelist

    tables when the file1 = file2 (i will need to make them lowercase in the process) and get a list of all files that are NOT

    in the diskimagelist table (meaning the files are listing in the database, but they are not actually on the hard drive)

    1 table

                Folder Name                                 Disfilename

     \\qqstore01\webimages\images\QQ001234\2006_07\ 1.JPG

     \\qqstore01\webimages\images\QQ001234\2006_07\ 10.doc

     \\qqstore01\webimages\images\QQ001234\2006_07\ 11.jpg

    2 table

      QQid,              Client_ID, FileName,Description, Directory,     Path

     QFWinData_QQ001234 8287 1.JPG PICTURE1 QQ001234\2006_07 \\QQStore01\webimages\images\

     QFWinData_QQ001234 5457 2.doc Welcome Letter QQ001234\2006_07 \\QQStore01\webimages\images\

     QFWinData_QQ001234 244 3.doc Welcome Letter QQ001234\2006_07 \\QQStore01\webimages\images\

    Here you go

  • Keeping aside the execution plans, the time constraint...here is one of the solns...

    SELECT * FROM databaseimageslist

    WHERE LOWER( DatabaseImagesList.Path + DatabaseImagesList.Directory + '\' + DatabaseImagesList.FileName ) NOT IN( SELECT LOWER( DiskImagesList.Folder + DiskImagesList.DiskFilename ) FROM diskimagelist )

    --Ramesh


  • Keeping aside the execution plans, the time constraint...here is one of the solns...

    SELECT * FROM databaseimageslist

    WHERE LOWER( DatabaseImagesList.Path + DatabaseImagesList.Directory + '\' + DatabaseImagesList.FileName ) NOT IN( SELECT LOWER( DiskImagesList.Folder + DiskImagesList.DiskFilename ) FROM diskimagelist )

    --Ramesh


  • It would help to post some example of the data in both tables. As far as I can see now, it is a simple select with LEFT JOIN and WHERE IS NULL... unless there is some complication, like that the path is stored in one table in a different format than in the other. For that, we need to have some example of data.

  • Ramesh,

    Thank you for your query, according to your query result, I found the imagefile on the hard drive(diskimagelisttable).  The fact is that I don't want to see the imagefile from DiskImagesList.Folder + DiskImagesList.DiskFilename at all, and that was what I can't accomplished for few days now...

    Thank you for your help

  • Try:

    SELECT LOWER(DatabaseImagesList.Path

            + DatabaseImagesList.Directory

            + '\' + DatabaseImagesList.FileName) AS MissingFile

    FROM DatabaseImagesList

            LEFT JOIN DiskImagesList ON LOWER(DatabaseImagesList.Path

                            + DatabaseImagesList.Directory

                            + '\' + DatabaseImagesList.FileName)

                                    = LOWER(DiskImagesList.Folder

                                    + DiskImagesList.DiskFilename)

                    AND DiskImagesList.DiskFilename IS NOT NULL

    WHERE DiskImagesList.DiskFilename IS NULL

    Andy

  • Andy,

     

    I tried this and it work

    "SELECT     A.FileName, A.Path + A.Directory AS [Physical Location], A.Description, A.QQID, A.Client_ID

    FROM         DatabaseImagesList A LEFT OUTER JOIN

                          DiskImagesList D ON D.DiskFilename = A.FileName

    WHERE     (D.DiskFilename IS NULL)

    ORDER BY A.QQID, A.Directory, A.Client_ID'"

    We need to have the report ready firsthing in the morning for Management.  

     

    Thank you for your help!!!

    Mai

     

     

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply