Help-a-newb with query ...

select distinct
threads.id,
threads.name,
threads.readonly,
threads.active,
threads.forumidfk,
threads.useridfk,
threads.author,
threads.datecreated,
forums.name as forum,
threads.author as username,
threads.sticky,
threads.views,
conferences.name as conference,
conferences.id as conferenceid,
threads.lastpost,
threads.msgcount as messagecount

from
(threads inner join forums on threads.forumidfk = forums.id)
inner join conferences on forums.conferenceidfk = conferences.id

where 1=1
<cfif arguments.bActiveOnly>
and threads.active = 1
</cfif>
<cfif isDefined("arguments.forumid")>
and threads.forumidfk = <cfqueryparam value="#arguments.forumid#" cfsqltype="CF_SQL_VARCHAR" maxlength="35">
</cfif>


======================================

... as it is now it works fine but I need to add the following condition :

<cfif isDefined("arguments.testOnly") and arguments.testOnly IS 1>
and users.testOnly = <cfqueryparam value="#arguments.testOnly#" cfsqltype="CF_SQL_SMALLINT">
</cfif>

please help with the [select] and [from] (inner join?). Thank you very much.

=====================================


order by threads.sticky desc, threads.lastpost desc

OK. I don't know MsSQL so this may be way off base.

But if I read your last condition correctly you want to filter the records according to the value of the users.testonly field. However the users table is not part of your FROM statement. Based on your select you'll need to change the FROM statement to something like:

from
((threads inner join users on threads.author = users.name)
inner join forums on threads.forumidfk = forums.id)
inner join conferences on forums.conferenceidfk = conferences.id

Basically you can't use a table in the WHERE clause that isn't part of the FROM clause.

Hope this helps.

Thank you very much "dykebert" I am almost getting there