I have two tables, each table needs

Question

Post reply

I have two tables, each table needs

Mai Nguyen-460836

SSC-Addicted

Points: 481
More actions
September 6, 2007 at 8:01 am

#374139

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

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

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

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

Vladan SSC-Insane Points: 22072 More actions · Answer 3

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.

Mai Nguyen-460836 SSC-Addicted Points: 481 More actions · Answer 4

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 5

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

Mai Nguyen-460836 SSC-Addicted Points: 481 More actions · Answer 6

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