Query Issue...

[palkema]

Hey all, I am having an issue here and i was hoping that somone could help.

I am trying to retrieve an outputted list of all results that don't equal eachother.

ie, I have two databases and i want to view what images are in one database that are not in the other database. Is there a way to do this?

This is what i am doing now:

<cfquery name="imagelist1" datasource="database1">
SELECT filename, imagename
FROM iod_product_images
</cfquery>


<cfquery name="imagelist2" datasource="database2">
SELECT filename, imagename
FROM iod_product_images
</cfquery>


<cfquery dbtype="query" name="final">
SELECT filename FROM imagelist1
WHERE imagelist1.filename = imagelist2.filename
</cfquery>




<cfoutput query="final">
<cfset count = '#count + 1#'>
</cfoutput>

<cfoutput>
#count#
</cfoutput>

[MichaelSJudd]

If you don't mind me asking, what is the issue you are having? Are you getting errors?

Anyway, I did notice in your count that it is not already set to 0. Personally I do the same thing, but like this:
<cfset count = 0>
<cfoutput query="final">
<cfset count = count+1>
</cfoutput>

If there is some other problem, can you be a bit more specific, and I'm sure someone can help out.

- Mike

[Dan_Bracuk · Accepted solution]

In some types of dbs, you can select from more than one db if you are properly set up. If not, you can do something like this.

query1
select somefield
from sometable etc

query2.
select whatever
from wherever
where somefield not in (the list from the first query)

[palkema]

Hey Guys,
I apologize for my poorly written question, I had a conference that i was supposed to attend and i was in the middle of writing the previous post when i realized that i was going to be late.

Sorry i did not explain myself well. :)

Anyway Dan Bracuk didn't actually solve the issue that i was having but he made me realize that what I was in error was in my sql statement. I had been pulling pulling, "* WHERE imagelist1.filename <> imagelist2.filename"

When i should have been using the NOT EXISTS sql function instead of the <>.

Thanks guys you rock!