Thanks, Doug... Sounds like a good plan. I have started breaking down the original join query to its indiviual queries and testing them.
I'm getting some good results with that testing, so it may have something to do with the original queries (although that worked in a different template) and could have to do with the test data that's in the database, although I've looked at that and haven't caught any problems. I'll stay at it and eventually get this figured out. Thanks for the advice! Rick -----Original Message----- From: Doug Boude (rhymes with 'loud') [mailto:[EMAIL PROTECTED] Sent: Saturday, April 01, 2006 7:37 AM To: CF-Talk Subject: Re: Anything inherently wrong with this query? Hi Rick. Just read through the entire thread, I see that you've reworked your query to utilize the suggested joins, and still you are getting query results that never change. If it were me in that situation, my very next step would be to grab the actual query out of debugging and start working with it directly in whatever query tool you have at hand to see if you are able to use that exact query and, by changing the value of the client id, get your database to give you back different results. Since it doesn't appear you're caching your query, I suspect the data itself at this point. Prove to yourself, if you already haven't, that your query is capable of retrieving different data sets by executing it with a varying where clause directly against your database. If the query is definately good and you are able to retrieve varying data sets in your query tool by changing the client ID, my next step would be to create an empty cfm page with nothing but this query in it. Set it up to receive the client ID via url, and see if you can then get CF to give you varying data sets using this test template. If you can, then it's time to either start dumbing down the original template by commenting things out until you find whatever anomoly is causing this issue, or start building up your test page by adding code snippets from your original template until you recreate the anomoly. This is how I would proceed in troubleshooting this one. Doug :0) >Hi, all... > >I've been wrestling with what should be a simple problem >for too many hours...I've got to break things down and >re-evaluate. Thought some more eyes might help. > >Anything inherently wrong with this? > >Rick > > <CFQUERY Name="Get_Client" Datasource="#DSN#"> > > Select C.*, FG.*, E.* > from clients C > inner join family_groups FG, employers E > on C.Family_ID = FG.Family_ID > and C.Employer_ID = E.Employer_ID > where C.Family_ID = > ( Select Family_ID > from clients > where Client_ID = '#Session.Client_ID#') > and C.Employer_ID = > ( Select Employer_ID > from clients > where Client_ID = '#Session.Client_ID#') > > </CFQUERY> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236774 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
