inequality question

[megalith]

I'm having trouble trying to run a query that returns rows that don't match.
The code below returns all the matching rows but what I need is all the image
names in the excelImages table that don't match the images in the
imagesOnServer table. Using the <> operator results in an error. Thanks for
any help on this.

<cfquery datasource="imagesDB" name="compare">
SELECT excelImages.imagename
FROM excelimages
INNER JOIN ImagesOnServer
ON excelImages.imagename = ImagesonServer.images
</cfquery>

[Dan Bracuk]

syntax varies with databases but you want to do something like
select imagename
from excelimages
where imagename not in
(select images from imagesonserver)

[megalith]

Thanks Dan.
I always forget to mention the DB. For this I am using Access. I tried this
code and it resulted in my CPU hanging at 95%. Nothing that a quick resart of
CF wouldn't fix. Any different approach for Access? thanks again for your
reply.

SELECT imagename
FROM excelimages
WHERE imagename NOT IN
(SELECT Images
FROM ImagesOnServer)

[vkunirs]

Hi
try this:

<cfquery datasource="imagesDB" name="compare">
SELECT excelImages.imagename
FROM excelimages
LEFT JOIN ImagesOnServer
ON excelImages.imagename = ImagesonServer.images
</cfquery>

[megalith]

Thanks vkunirs but that returns all the images. What I need is all the excelimages that aren't in the imagesonserver table. Anyone else have an idea for me? thanks

[paross1]

<cfquery datasource="imagesDB" name="compare">
SELECT e.imagename
FROM excelimages e
WHERE NOT EXISTS (SELECT 1
FROM ImagesOnServer i
WHERE e.imagename = i.images)
</cfquery>

Phil

[vkunirs]

Hi

try by giving the where condition.

<cfquery datasource="imagesDB" name="compare">
SELECT excelImages.imagename
FROM excelimages
LEFT JOIN ImagesOnServer
ON excelImages.imagename = ImagesonServer.images
where excelImages.imagename <> ImagesonServer.images
</cfquery>

if still this does not work then try with MINUS.
like

select * from table1
minus
select * from table2.

[paross1]

Srinivas, I'm curious how you would expect a query to return any rows where
excelImages.imagename = ImagesonServer.images and excelImages.imagename <>
ImagesonServer.images at the same time.

Phil ;)

[megalith]

Thanks Phil but even that didn't seem to work. After 10 minutes I gave up
waiting and restarted the windows swsoc.exe service and the CF database
service. It showed no signs of stopping. One table has 27,500 rows and the
other had 26,800. Not sure if that's so big that this type of query should take
so long.

Anyways, since I had the equality working I decided to backup the DB and then
just run a DELETE all where records matched. I was then left with the records I
have been trying to get at.

I would still like to find a query that will do what we have been trying to
do. Thanks everyone for your efforts on this.

[paross1]

Try adding an index to the imagename field in the excelimages table, and the images field in the imagesonserver table (if one does not exist).

Phil

[megalith]

Phil,
One table had an index already but the other didn't. I added the index to the
other table, ran your query, and it finished in about 10 minutes. Seems strange
that it can run the equality query in 1 second but the inequality takes 10
minutes?

Either way I appreciate your help and everyone elses. I now know how to write
the query, I just have to be very patient for an answer from the DB.

[paross1]

Glad that this worked for you, although 10 minutes is still way too long to
have to wait for a query to run, especially if this is online.

When was the last time that you performed a repair and compact on your
database? I used to have an application that used an Access database, and when
the db file got over approximately 30 MB in size, my queries would ground to a
halt after a data import until I would run compact. (I finally migrated it to
SQL Server, since Access tends to get squirrelly when it reaches certain size
limits, etc.)

Phil

[megalith]

Phil,

10 minutes does seem way too long to me also. the select statement for
equality runs in 1 second. The Access DB is only 3mb and it has indexes and
was compacted and repaired before I ran the query. Kinda strange. I wonder if
using MySQL would make a difference. Maybe if I have some spare time I will
convert it to MySQL and give it a try. Thanks for your input.

[MikerRoo]

First let me explain why <> is so slow.
It is because the DB must scan the indices (or even the table) completely for
every "left term" row.

In essence you lose most of the benefits of indices!
This is not an Access problem, it is true of all RDBMS.

That said, the attached query should be more efficient:



<CFQUERY datasource="imagesDB" name="compare">
SELECT
excelImages.imagename
FROM
excelimages
LEFT JOIN
ImagesOnServer ON excelImages.imagename = ImagesonServer.images
WHERE
ImagesonServer.images IS NULL
</CFQUERY>

[megalith]

MikerRoo,

That did the trick. The query took 2 seconds to run and came back with the correct quantity. Thanks for the code!