[CFTALKTor] How do I filter matching records?

[Arun Persaud]

The values for #category#, #ReviewCount# and #reviews# are being passed from a previous page.

 

<cfoutput>
<CFSET MAXROWS = #reviews#>
<CFSET checkReviewCount = #ReviewCount#>
</cfoutput>

 

<cfquery name="checkCategory" datasource="DB" MAXROWS="#MaxRows#">
SELECT table1.*, table2.*
FROM table1, table2
WHERE table1.category LIKE '%#category#%'

AND table2.category LIKE '%#category#%'

AND table2.Reviews <> #checkReviewCount#
</cfquery>

 

 

 

 

 

From the above Query, I'm trying to Output only the records that have the same category from two tables. Of these records, from TABLE2 I want only the ones where the "Reviews" DO NOT EQUAL the "checkReviewCount".

 

For example: If MaxRows = 3

 

TABLE2:COLUMN1
CheckID (autonumber) 
1       
2        
3

 

TABLE2:COLUMN2
Reviews  (int)
4        
2        
7 
     
TABLE2:COLUMN3
checkReviewCount (int)
4
0
0

 

TABLE2:COLUMN4
category (text)
dogs
dogs
dogs

 

Only 2 records should be returned, since the number 4 occurs in both the "Reviews" and "checkReviewCount" Columns.

 

Do I have to loop the query to get a value for each occurance of #checkReviewCount#.  If so, how do I accomplish this?

 

Thank You.

 

 

Arun