Compare 2 queries

[etman]

I'm running CFMX + MySQL 3.23

I've got 2 queries happening - one queries the db, the other "query" gets a directory listing from a particular folder of images. I'd like to do the equivalent of an INNER JOIN between the two, where I'm only interested in the results where there are images located on the server. I've already tried different ways of joining the two, but that's not working - JOINing on a query of query isn't liked by CF.

I tried dumping the results of the directory listing into a temporary table and then adding that as an INNER JOIN WHERE image_id IS NOT NULL, but the server kept timing out (I don't have control over the server settings, and it WAS taking a long time to run).

My work around so far is to write a script that will update the db every time that directory is updated, but it's really the best solution. Any suggestions or leads in the right direction would be greatly appreciated.

Thanks in advance.
Rob

[Dan_Bracuk]

When you do a query of query, you must join the queries in your where clause, not the from clause.

select stuff
from query1, query2
where query1.somefield = query2.somefield

Can't use alias query names either. Bottom line, lotsa typing.

(later, after reading the question again)

and query1.anotherfield not in (#valuelist(query2.someotherfield)#)
or quotedvaluelist if it's text.

[etman]

Thanks for the reply Dan, but no go (unless i completely coded it wrong) - valuelist and quotedvaluelist both threw query of query runtime errors.

I may not have explained it properly though. Here's another try;

Query 1 queries the db to get info about a product search; one of the criteria I'd like to affect this search is whether or not the results of that query have a corresponding image on the server. That's where i'm using a <cfdirectory> call to get the contents of the directory. Oh - and the contents of this directory all have .jpg as the extension; but that needs to be taken out of the query.

So, Query 1 returns a bunch of info; one of which is a product code. The directory query returns the names of all the files in that directory (which are all #product_code#.jpg, but all have a .jpg on them. What i want to do is see which products have a corresponding image in the image directory - but all have a .jpg extension - and return only these as valid products.

Thanks,
Rob

[Dan_Bracuk]

Let's call the directory query q2. To strip the jpg extenstions do this.

newlist="";
q2list = valuelist(q2.fieldname);
for (i=1; i= q2.recordcount; i = i + 1) {
ThisItem = ListGetAt(q2list, i);
newlist = ListAppend(newlist, ListFirst(ThisItem, "."));
}

Your q of q will then resembles

select stuff
from query1
where product_code not in (#newlist#) // for numeric codes

or
where product_code not in (#ListQualify(newlist, "'")#) // for text codes