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)
Art_ID   Km          Price 
1        10000        5000
2        12000        6000
Artikel_ID    Extras
1             10
1             20
2             10
<cffunction name="cleanOrderBy" returntype="string">
  <cfargument name="theValue" type="string">
  <cfargument name="defaultSort" type="string">
  <cfif REFindNoCase("[\w,]{1,50}\s+(asc|desc)\s*",theValue,1)>
    <cfset matches = REFindNoCase("[\w,]{1,50}\s+(asc|desc)\s*",theValue,1,"true")>
    <cfreturn Mid(theValue,matches.pos[1],matches.len[1])/>
  <cfreturn defaultSort/>
<cfparam name="PageNum_dboartikel" default="1">
<cfparam name="url.tfm_orderby" default="Price">
<cfparam name="url.tfm_order" default="ASC">
<cfset sql_orderby = cleanOrderBy("#tfm_orderby# #tfm_order#","Price")>
<cfif true eq true>
  <cfquery name="dboartikel" datasource="carfree24">
SELECT a.Art_ID,a.Km,a.Price,ArtExtras.Extras
FROM Artikel a
LEFT JOIN ArtExtras ON (a.Art_ID = ArtExtras.Artikel_ID)
WHERE (0=0
<cfif isDefined ("Url.S_Preisbis")>
 <cfif Url.S_Preisbis NEQ "">
 AND  a.Price <= <cfqueryparam value="#URL.S_Preisbis#" cfsqltype="cf_sql_numeric">
<cfif isDefined ("Url.S_Kilometerbis")>
 <cfif Url.S_Kilometerbis NEQ "">
 AND   Km <= #URL.S_Kilometerbis#
<cfif isdefined("Url.S_Extras")>
 <cfif Url.S_Extras NEQ "">     
 AND   EXTRAS IN (<cfqueryparam value="#URL.S_Extras#" cfsqltype="cf_sql_numeric" list="yes">)
     </cfif> )
ORDER BY #tfm_orderby# #tfm_order#
  <cfset QueryString_dboartikel=Iif(CGI.QUERY_STRING NEQ "",DE("&"&CGI.QUERY_STRING),DE(""))>
<cfset MaxRows_dboartikel=10>
<cfset StartRow_dboartikel=Min((PageNum_dboartikel-1)*MaxRows_dboartikel+1,Max(dboartikel.RecordCount,1))>
<cfset EndRow_dboartikel=Min(StartRow_dboartikel+MaxRows_dboartikel-1,dboartikel.RecordCount)>
<cfset TotalPages_dboartikel=Ceiling(dboartikel.RecordCount/MaxRows_dboartikel)>
//sort column headers for dboartikel
tfm_saveParams = "";
tfm_keepParams = "";
if(tfm_order EQ "ASC") {
        tfm_order = "DESC";
        tfm_order = "ASC";
if(ListLen(tfm_saveParams)GT 0) {
        For (i=1; i LTE ArrayLen(tfm_params); i=i+1) {
                if(isDefined(tfm_params[i])) {
                        tfm_temp = tfm_params[i];
                        if(isDefined("form." & tfm_params[i])) tfm_temp = "form." & tfm_params[i];
                        tfm_keepParams = tfm_keepParams &  LCase(tfm_params[i]) & "=" & URLEncodedFormat(Evaluate(tfm_temp)) & "&";
tfm_orderbyURL = CGI.SCRIPT_NAME & "?" & tfm_keepParams & "tfm_order=" & tfm_order & "&tfm_orderby=";
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<cfoutput query="dboartikel" group="#tfm_orderby#" startRow="#StartRow_dboartikel#" maxRows="#MaxRows_dboartikel#">
  <tr><td colspan="3">
  <table width="300px" border="2" cellpadding="0" cellspacing="0" bordercolor="##00FF00">
      <td colspan="2"> </td>
      <td><a href="#tfm_orderbyURL#Km">Kilo</a></td>
      <td><a href="#tfm_orderbyURL#Price">Preis</a></td>
      <td height="142" colspan="2">
  <cfif #Extras# NEQ "">

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

i recon both - your "wrong" recordcount and "extra" results - are because of this line:
AND   EXTRAS IN (<cfqueryparam value="#URL.S_Extras#" cfsqltype="cf_sql_numeric" list="yes">)

the IN operator will select ALL records that match ANY of the values in the list.

you need to loop through #URL.S_Extras# list and create a series of "AND Extras = xxx" statements instead of using the IN operator:

<cfif isdefined("Url.S_Extras") AND listlen(url.s_extras)>
  <cfloop list="#url.s_extras#" index="extra">  
   AND EXTRAS = <cfqueryparam value="#extra#" cfsqltype="cf_sql_numeric">

btw, you can easily combine your paired <cfif> statements into one:
instead of
<cfif isDefined ("Url.S_Preisbis")>
 <cfif Url.S_Preisbis NEQ "">
(and other similar code lines)

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.
PanosAuthor Commented:
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 and so i changed the group to:
group="art_ID". and the output:
<cfif #Extras# NEQ "">
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:

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
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.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I don't see anywhere in your query where you try to limit it to just art_Id=1

how about adding the clause...

 AND   ART_ID = <cfqueryparam value="#ART_ID#" cfsqltype="cf_sql_numeric">
PanosAuthor Commented:
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">)
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  
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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...
Try to select DISTINCT rows:

SELECT DISTINCT a.Art_ID,a.Km,a.Price,ArtExtras.Extras
FROM Artikel a .....

and so on.

PanosAuthor Commented:
Thank you all for help.
but keep

if you want to get any records in your resultset
i see the a earlier answer is accepted - thanks, but still try to use DISTINCT
sorry, i had to reread your question several times, now I think I know what you're going for.  You want the records where BOTH the extras exist.

So that would be this...
<cfquery name="dboartikel" datasource="carfree24">
 SELECT a.Art_ID,a.Km,a.Price
  FROM Artikel a
 WHERE 1=1
<cfif isDefined ("Url.S_Preisbis") and len(Url.S_Preisbis)>
 AND  a.Price <= <cfqueryparam value="#URL.S_Preisbis#" cfsqltype="cf_sql_numeric">
<cfif isDefined ("Url.S_Kilometerbis") and len(Url.S_Kilometerbis)>
 AND   Km <= #URL.S_Kilometerbis#
<cfif isdefined("Url.S_Extras") and len(Url.S_Extras)>
  <cfloop index="aValue" list="#Url.S_Extras#">
   and exists (select top 1 ae.extras from ArtExtras ae where ae.Artikel_ID = a.Art_ID and ae.EXTRAS = <cfqueryparam value="#aValue#" cfsqltype="cf_sql_numeric">)
 ORDER BY #tfm_orderby# #tfm_order#

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.