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>
______________________________________________________________________
Why Share?
Dedicated Win 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=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
