That's one of the things I was considering....but I was hoping for a
"shortcut" to counting the "groups"....because I'm also using a "previous 1
2 3 4 5 ... next" routine to allow for navigation through the recordset, and
can't figure out any way to calculate page startrows except for rowcount...
Thanks anyway!
D
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 12, 2001 2:17 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: currentrow with join query
> There must be a way to do this...but it escapes me:
>
> I have this query:
> <cfquery name=get datasource=#dsn#>
> SELECT p.projectID, p.projectname, p.userID, p.startdate,
> p.totalrevenue, p.probability, co.contactID, co.fname, co.lname,
> co.email, co.businessphone, cl.company, cl.clientID
> FROM projects p RIGHT JOIN (contacts co INNER JOIN clients cl
> ON cl.clientID = co.clientID) ON p.clientID = co.clientID
> WHERE cast(p.userID as varchar) in
> (#rep#)#preservesinglequotes(string1)#
> GROUP BY p.projectID, p.projectname, p.userID, p.startdate,
> p.totalrevenue, p.probability, co.contactID, cl.clientID,
> co.fname, co.lname, co.email, co.businessphone, cl.company
> ORDER BY p.userID
> </cfquery>
>
> I'm using maxrows/startrow to paginate the results....
>
> The problem is: I'm sorting on projectID...but there can be
> multiple contact records for each project one....
>
> SO, <cfoutput query=get group=projectID
> startrow=#thisstartrow# maxrows=25>
> will give me 25 rows, but that may only be 18 projects....and
> the numbering scheme is thrown off all down the line....
>
> currentrow counts ALL the returned rows....is there a
> reasonably easy way to tell how many of the *group* records there
> are....and figure maxrows and startrows from that?
First of all, you don't appear to need the GROUP BY clause in your query,
since you're not using any aggregate functions. GROUP BY isn't required for
using the GROUP attribute of CFOUTPUT.
Second, you're right about how MAXROWS and the CurrentRow values - they're
not going to show you how many "group header" records you have. There are
several approaches you might follow to fix your problem. You might try using
a counter within your output block to track each "group header" record, then
stop outputting records after a set number.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
