Not Equal Problem

[larrygot]

When I do a query checking for equality between two fields in two different tables, it runs fine ... but when I change the query to return records that are not equal the server runs forever and the page hangs and never displays. The datasource is an Access database using ODBC. ColdFusion Server is 4.5 . What am I doing wrong?

Here's the code:

<CFQUERY NAME="MatchQuery1" DATASOURCE="CellAnalSystem">
Select * From LKE_POCs, Main
Where (LKE_POCs.Cell_Number <> Main.Cell_Number) And Main.BillDate = '200601'
</CFQUERY>

<CFOUTPUT query="MatchQuery1">
#Cell_Number# #Family_Name# <BR>
</cfoutput>

 

[mansii]

You need a JOIN statement here. Hundreds of usefull tips in search page.

Good luck,
mansii

 

[r937]

mansii, that was a join -- any query with more than one table in the FROM clause is a join

however, explicit JOIN syntax is better

larrygot, why are you looking to "return records that are not equal"

could you explain what you're trying to do?

r937.com | rudy.ca

 

[mansii]

Yes, Rudy. I remember having a JOIN problem long time ago. Then you came up with an exact answer to the problem.
I thought that Larrygot is dealing with a similar problem here that I suggest him to search for the answer, hoping that he found the answer that you gave me.

Regards,
mansii

 

[larrygot]

We're trying to return a set of records that match the criteria and another set of records that do not match. We have now used a sub-query and that seems to work, but we don't understand why just using <> causes the server to hang, but = returns results very quickly.

 

[r937]

ask yourself this:

for a single row in the LKE_POCs table, grab its Cell_Number

now, how many rows in the Main table have a Cell_Number that is not equal to the Cell_Number you're looking at?

that's right, most of them

okay, now grab another Cell_Number from LKE_POCs

how many rows in the Main table have a Cell_Number that is not equal to this one?

that's right, most of them

repeat this for every row in LKE_POCs

in total, how many times did you bring back most of the Main table?

and that's why it brings the server to its knees

r937.com | rudy.ca