Problem in recordcount with group

Hi experts.
Look at the code below.The problem is that i have wrong recordcount values.
if one art_ID has f.e two values in ArtExtras table like art_ID 1 than if i want to show all items in my resultpage (They are 2 now) i have recordcount 3 and not 2.
The select query does not work good too.
If i have selected two values for S_Extras in URL(S_Extras=10&S_Extras=20) i have in my result page both art_id 1 and art_id 2 as result.(I should have only the item with art_Id 1 that has in the ArtExtras Table the values 10 and 20 as Extras)
The hole function with recorcount and Maxrows... does not work good.
Any help?
(i have CF8 and MSSQL)

just use
<cfif isDefined ("Url.S_Preisbis") AND Url.S_Preisbis NEQ "">
CF uses short-cut evaluation of compound if statements. in the above case it means that if the first statement (isDefined) is false the second statement will not even be looked at by CF.

Hi azadisaryev.
In tried to change my question but you did already make the post and so i did not.
I followed the tutorial on http://tutorial150.easycfm.com/ and so i changed the group to:
group="art_ID". and the output:
<cfif #Extras# NEQ "">
<cfoutput>
#Extras#,
</cfoutput>
</cfif>
Also i copied your code in my SELECT query.
Now i have the same problem with recordcount and when i select:
S_Extras=10&S_Extras=20 i have no results.

Of course, there won't ber any records matching your WHERE clause if you follow suggestion to loop through list #url.s_extras# like this <cfloop>AND EXTRAS =..</cfloop>. This code will generate WHERE clause as:
AND EXTRAS=1 AND EXTRAS=2

No record can can have EXTRAS match two values at the same time. If you want your query to return any result at all then, you have to generate either
AND EXTRAS IN (1,2)
either
AND (EXTRAS=1 OR EXTRAS=2)
Please note proper use of parenthises (makes whole lot of difference in SQL). If you get too many records add more specific conditions to your WHERE clause.

In any case, before you start banging your head on the wall with coldfusion code, make sure you have written database query that returns results you need. Only after that you start working on the code that would generate query dynamically. Always start you work at the data level.

Hi gdemaria.
Why Art_ID?
This is the values i'm trying to find out.Which art_ID in table artikel have the specified in Url (S_Extras)values in table ArtExtras(relationship one to many) .

Hi eszaq.
If you mean i must change the code to:
<cfif isdefined("Url.S_Extras") AND Url.Extras NEQ "">
AND EXTRAS IN (<cfqueryparam value="#URL.S_Extras#" cfsqltype="cf_sql_numeric" list="yes">)
</cfif>
i have the problem i discribe in my question.

I understand that you have a problem with that, but what I am saying is: there is NO WAY your query will return any results if generated query will have something like
WHERE EXTRAS=1 AND EXTRAS=2
It is impossible, because following this logic you will be trying to find rows where 1=2 (if a=b and a=c then b=c).

This is problem with your SQL, not Coldfusion. You need to post your tables structure and what you need to pull from database in SQL Experts area (skip all the coldfision code there to avoid confusion). Once you get your query straighten up and working in the database move to CFML scripts.

what you want to do is extremely hard, if not impossible, to achieve in one query...
consider instead:
a) run a db query to select records with the EXTRAS IN (<cfqueryparam value="#url.S_Extras#" list="yes">) - it will select both art_id=1 and art_id=2
b) run a QoQ on the above query to select only records which have first url.S_Extras
c) run a QoQ on the db query to select only records that have the second url.S_Extras
d) run a QoQ on the above 2 QoQs , INNER JOINing them on art_id column - this shoudl return you the art_ids that have only both S-Extras

mind you, the above is untested, but should work.

if you are using MySQL db, it has a GROUP_CONCAT() function, which concatenates ROWS into a delimited list - you may play with that and various string functions...

Featured Post

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.