Comparing DB rows and files

[Guest]

I have a site that has a table with filenames of photos

within the site. I need to be able to find orphaned info so I can keep the

DB and file system clean. Here is what I am looking to do:

Pull all rows from the photos table and run a cfdirectory on the directory where they are stored. I need to be able to compare the two so I can see files that have no associated DB record and DB records that have no file associated.

Any ideas are appreciated. Thanks.

Tim

[Dan_Bracuk]

Query of Queries

[JaneUK]

What quantity of data are you looking at? If it's reasonably small, then query of queries is the way to go. If not, you may be better handling this at database level rather than within CF - and how you do that depends on your database, and whether the storage directory is on the same server as the DB.

[Guest]

So assuming I have the following, can you help me with the QoQ?

Directory Listing Column

getPhotos.name

DB Column

getDBRows.fileName

How do I use a QoQ to give me a recordset of missing files or DB rows?

Thanks.

[Dan_Bracuk]

Here are two more hints.

sql part

where somefield not in (  )

Coldfusion part

valuelist()