Order By or sorting nested querys

[ICI-MASA]

Hi,

I have this code for example

<cfquery name="rsRights" datasource="cf_db">
SELECT RID, UID, PID FROM tbRights WHERE PID = #URL.PID#
</cfquery>

<cfloop query="rsRights">
<cfquery name="rsUsers" datasource="cf_db">
SELECT UID, UfirstName, UlastName FROM tbUsers WHERE UID =
<cfoutput>#rsRights.UID#</cfoutput>
</cfquery>

<cfoutput>#rsUsers.UfirstName#</cfoutput>&nbsp;<cfoutput>#rsUsers.UlastName#</cf
output>
</cfloop>

Now because it is the rsRights query that I am looping how do make an ORDER BY
for the rsUsers? Because it does not help to put Order By in the rsUsers Query!
or do I have the wrong approch on this?

Regards
Martin

[vkunirs]

Hi

can u try like this:

<cfloop query="rsRights">
<cfquery name="rsUsers" datasource="cf_db">
SELECT UID, UfirstName, UlastName FROM tbUsers WHERE UID =
<cfoutput>#rsRights.UID#</cfoutput> order by UID
</cfquery>
</cfloop>

Is this what you want?
on which field you want to sort and in which table it is.

[ICI-MASA]

Hi,

sorry i forgot to say that the field I wanted to sort is UfirstName, and I
tried to place a order by UfirstName where you placed the order by UID but that
doesnt work, because I guess the loop is for the rsRights query or?

regards
Martin

[paross1]

Perhaps...

<cfquery name="rsUsers" datasource="cf_db">
SELECT tu.UID, tu.UfirstName, tu.UlastName
FROM tbUsers tu INNER JOIN tbRights tr ON tu.UID = tr.UID
WHERE tr.PID = #URL.PID#
</cfquery>

<cfoutput query="rsUsers">#UfirstName# &nbsp UlastName#</cfoutput

[ICI-MASA]

Thanks Paross,

that was the way to go...

regards
Martin