Thank you! I did fully qualify all my variables, as my example showed, so that wasn't it. But I didn't select the column I was sorting by, and that did it!
At 02:14 PM 2/18/02 -0800, Mark A. Kruger - CFG wrote: >You need to qualify your query correctly. getThese.SEQ is NOT included in >your select list, because GetThis is first. Try this: > ><cfquery name="getSortedJoin" dbtype="query"> > SELECT GetThese.THISID AS ThisID, GetThis.THESEID AS TheseID, >GetThese.SEQ AS SEQ > FROM getThis, getThese > WHERE getThis.ThisID = getThese.ThisID > ORDER BY getThese.SEQ ></cfquery> > >Note, on a Q of a Q join, you should qualify each column with it's original >querey name (and alias it if you don't want to go nuts in your cfoutput). >The column you intend on sorting by must be included in the select column >list. > >Mark > >P.S. - There's an article on Q of a Q available at cfwebtools resources: >http://www.cfwebtools.com/index_dev.cfm > > > >-----Original Message----- >From: Peter Theobald [mailto:[EMAIL PROTECTED]] >Sent: Monday, February 18, 2002 11:14 AM >To: CF-Talk >Subject: Query of Query cannot sort > > >Help! This should be simple. Have I found a bug in Queries of Queries? > >I have two queries to the database. Later I want to join the data and sort >it by an INTEGER column called 'SEQ'. I use a query of a query to do the >select in memory, but I get the following error: > >GETTHESE.SEQ is not a column that can be sorted on. > >Here is the relevant code: > >Tests for bug in query of query >Database: > Table This ( (ONE of This) > ThisID int > ThisName varchar(50) > ) > Table These ( (to MANY of These) > TheseID int > TheseName varchar(50) > ThisID int (foreign key to This.ThisID) > SEQ int (Sequence for sorting) > )>=============== > ><cfquery name="getThis" datasource="demo"> > select ThisID, ThisName from This > where ThisID=1 ></cfquery> ><cfquery name="getThese" datasource="demo"> > select TheseID, ThisID, SEQ, TheseName from These ></cfquery> ><cfquery name="getSortedJoin" dbtype="query"> > select ThisID, TheseID from getThis, getThese > where getThis.ThisID = getThese.ThisID > order by getThese.SEQ ></cfquery> > ><cfoutput query="getSortedJoin"> > #ThisID#, #TheseID#<br> ></cfoutput> > > > > ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
