I've got an inner join that I'm trying to make work with paging the records and I *think* I see what the problem is, but I don't know how to proceed...
The inner joins are working correctly. In fact, when I go to output, I simply group them by property ID, and I get the right number of Properties, and the right rates for each property...but the pagination is ALL wonky. I'm using Dreamweaver's Next N results, which is fairly easy to understand, and normally works great... Here's my query: <cfif IsDefined("URL.Bedrooms")> <cfquery name="getMyListings" datasource="#Application.DSN#"> SELECT * FROM (tblRentalLocations INNER JOIN tblRentalProperties ON tblRentalLocations.LocationID=tblRentalProperties.frn_LocationID) INNER JOIN tblRentalRates ON tblRentalProperties.PropertyID=tblRentalRates.frn_PropertyID <cfif URL.Bedrooms NEQ 'ANY'> WHERE tblRentalProperties.PropertyBedrooms = #URL.Bedrooms# </cfif> ORDER BY tblRentalProperties.PropertyName, tblRentalRates.RatesOrder, tblRentalRates.RatesLength Desc </cfquery> </cfif> My Next N code looks like this: <cfset MaxRows_getMyListings=10> <cfset StartRow_getMyListings=Min((PageNum_getMyListings-1)*MaxRows_getMyListings+1 ,Max(getMyListings.RecordCount,1))> <cfset EndRow_getMyListings=Min(StartRow_getMyListings+MaxRows_getMyListings-1,getM yListings.RecordCount)> <cfset TotalPages_getMyListings=Ceiling(getMyListings.RecordCount/MaxRows_getMyList ings)> <cfset QueryString_getMyListings=Iif(CGI.QUERY_STRING NEQ "",DE("&"&CGI.QUERY_STRING),DE(""))> <cfset tempPos=ListContainsNoCase(QueryString_getMyListings,"PageNum_getMyListings= ","&")> <cfif tempPos NEQ 0> <cfset QueryString_getMyListings=ListDeleteAt(QueryString_getMyListings,tempPos,"&" )> </cfif> Now, here's where it gets wonky. On the query, the actual recordcount is the total number of rows returned, when I use the grouped output and nest my cfoutput tags, it works perfectly, but it gives me a wrong "recordCount" and that, in turn, throws the next n browsing all to heck and back. I THINK I should somehow be totalling the number of unique PropertyIDs there are in the query, and use that instead of "recordCount"...am I on the right track? I tried COUNT(PropertyID) AS PropertyCount but that didn't work, it won't let you use that AND a * in the same query, and when I went to all the trouble to place all my fieldnames in there, it didn't work either... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4