<html>
<head>
<title>QueryNew Example</title>
</head>
<body>
<H3>QueryNew Example</H3>
<head>
<title>QueryNew Example</title>
</head>
<body>
<H3>QueryNew Example</H3>
<cfparam name="category" default="dogs">
<cfparam name="checkReviewCount" default="4">
<cfparam name="reviews" default="4">
<cfset table1 = QueryNew("category")>
<cfset table2 = QueryNew("CheckID, Reviews, checkReviewCount, category")>
<!--- make some rows in the query --->
<cfset newRow = QueryAddRow(table1, 3)>
<cfset temp = QuerySetCell(table1, "category", "dogs", 1)>
<cfset temp = QuerySetCell(table1, "category", "dogs", 2)>
<cfset temp = QuerySetCell(table1, "category", "dogs", 3)>
<!--- set the cells in the query --->
<cfset newRow = QueryAddRow(table2, 3)>
<cfset temp = QuerySetCell(table2, "category", "dogs", 1)>
<cfset temp = QuerySetCell(table2, "category", "dogs", 2)>
<cfset temp = QuerySetCell(table2, "category", "dogs", 3)>
<cfset temp = QuerySetCell(table2, "checkID", 1, 1)>
<cfset temp = QuerySetCell(table2, "checkID", 1, 2)>
<cfset temp = QuerySetCell(table2, "checkID", 1, 3)>
<cfset temp = QuerySetCell(table2, "reviews", 4, 1)>
<cfset temp = QuerySetCell(table2, "reviews", 2, 2)>
<cfset temp = QuerySetCell(table2, "reviews", 7, 3)>
<cfset temp = QuerySetCell(table2, "checkReviewCount", 4, 1)>
<cfset temp = QuerySetCell(table2, "checkReviewCount", 4, 2)>
<cfset temp = QuerySetCell(table2, "checkReviewCount", 4, 3)>
<!--- output the query --->
<cfquery dbtype = "query" name = "QueryFromQuery">
select table2.checkID as ck, table2.reviews as rev
from table2
join table1 ON table1.category = table2.category
where table1.category like '%#variables.category#%'
AND table2.checkReviewCount <> #variables.checkReviewCount#
</cfquery>
<cfquery dbtype = "query" name = "QueryFromQuery">
select table2.checkID as ck, table2.reviews as rev
from table2
join table1 ON table1.category = table2.category
where table1.category like '%#variables.category#%'
AND table2.checkReviewCount <> #variables.checkReviewCount#
</cfquery>
<cfoutput query="queryfromquery">
#queryfromquery.ck# #rev#<br>
</cfoutput>
#queryfromquery.ck# #rev#<br>
</cfoutput>
</body>
</html>
Steve Budan
-----Original Message-----
From: Arun Persaud
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 18, 2002 3:01
PM
To: [EMAIL PROTECTED]
Subject: [CFTALKTor] How do I
filter matching records?
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 = 3TABLE2:COLUMN1
CheckID (autonumber)
1
2
3TABLE2:COLUMN2
Reviews (int)
4
2
7
TABLE2:COLUMN3
checkReviewCount (int)
4
0
0TABLE2:COLUMN4
category (text)
dogs
dogs
dogsOnly 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
