Webmaster Forum / ColdFusion / Getting Started / January 2007

But wouldn't I then need 15 If/else statements?  What if I have 100 variables?

Isn't there a way to search the list of values for my variable in query 1 and
then display all
from query one and the remainder from query two?

But wouldn't I then need 15 If/else statements?  What if I have 100 variables?

Isn't there a way to search the list of values for my variable in query 1 and
then display all
from query one and the remainder from query two?

I'm sort of succeeding.  I have two remaining problems: 1) I'm rendering the
results as two contiguous HTML tables, which prevents alphabetizing; and 2) my
results are being displayed by a simple condition.  The problem here is that it
only works when the <cfif> is met.  When the test fails and we go to <cfelse>
for some reason THEN I get a syntax error on my QofQ WHERE clause.

<cfquery name="completed" datasource="req">
SELECT *
FROM tbl_Req_SiteSpecific
WHERE (tbl_Req_SiteSpecific.ID='#cookie.PID#') AND
(tbl_Req_SiteSpecific.`Clinical Site Name`='#cookie.SelectBox#')
</cfquery>

<cfquery name="q_reqmnts_f3" datasource="req" dbtype="odbc">
SELECT Req_SiteID, C_ID, C_Name, Req_Cat, Req_ReqID, Req, Resp_s, Resp_Site,
R_Time, R_today  
FROM Q_req_reqmnts
WHERE (C_Name = '#cookie.SelectBox#' and Resp_s = -1)
ORDER BY Req_Cat
</cfquery>

<cfquery name="unique_reqIDs" dbtype="query">
SELECT Req_reqID, req
FROM q_reqmnts_f3
WHERE Req_reqID NOT IN (#valueList(completed.Req_reqID)#)
</cfquery>

conditional:  <cfif #completed.RecordCount# gt 0>

Thanks for any feedback!

Rick

When I try to run this:

<cfquery name="new_query" datasource="req">
SELECT q.Req_reqID, q.req
FROM Q_req_reqmnts q
WHERE q.C_Name = '#cookie.SelectBox#'
AND q.Resp_s = -1
AND NOT EXISTS (SELECT 1
FROM tbl_Req_SiteSpecific r
WHERE r.tbl_Req_SiteSpecific.ID='#cookie.PID#'
AND r.tbl_Req_SiteSpecific.`Clinical Site Name`='#cookie.SelectBox#'
AND r.Req_reqID = q.Req_reqID)
</cfquery>

I get this:

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Too few parameters. Expected 2.

OK.  What this is doing is successfully giving me the all the values not
already in table A.

The problem is I want to include those values from table A, if present, and if
not, take them
from table b.  It is the "c list" below.  However, my HTML needs to include
the a list, preferrably
alphabetized within the additional values from table b.

Here is the a list: 33,31,68
Here is the b list: 101,3,4,12,12,52,69,22,2,31,5,8,7,6,13,1,68,126
Here is the c list: 5,6,7,2,3,1,8,13,52,22,12,12,69,126,101,4

Thanks again,

Rick

Remove the single quotes......

WHERE completed.SiteID = new_query.C_ID

Phil