two tables and using a CFIF tag

You can do this with a single query using a left join. The syntax depends on your db, but in Oracle it would be something like this

select Subject_DescriptionID, School, coalesce(Subject, "no description available") theSubject

from school s left join subject_description sd on s.schoolID = sd.schoolID

where SubjectID=<cfqueryparam value=#form.subject# cfsqltype="cf_sql_integer">

Hi Dan...these are the queries in it. Can you help ?

Cheers
Peter


<cfquery name="subject_description" datasource="mysqlcf_madeinusa" >
select
subject_description.Subject_DescriptionID,schoolid_subjectid.SubjectID,subject_description.SubjectID,schoolid_subjectid.SchoolID,subject_description.Subject
from
subject_description,schoolid_subjectid
WHERE
subject_description.SubjectID = schoolid_subjectid.SubjectID
AND subject_description.SchoolID = schoolid_subjectid.SchoolID

</cfquery>


<!--- search by subject--->
<cfif isdefined("form.searchsub")>
<cfif isdefined("form.subject") and form.subject neq "">

<cfquery name="selectsub_school" datasource="mysqlcf_madeinusa" >
select schools.SchoolID,schools.School,schoolid_subjectid.SchoolID,schoolid_subjectid.SubjectID
from
schools,schoolid_subjectid
where
schoolid_subjectid.SubjectID=<cfqueryparam value=#form.subject# cfsqltype="cf_sql_integer">
and
schools.SchoolID=schoolid_subjectid.SchoolID
</cfquery>


<cfif selectsub_school.recordcount gt 0>
</cfif>
</cfif>
</cfif>
</a></h3>
<cfif isdefined("form.searchsub")>
<cfif isdefined("form.subject") and form.subject neq "">
<cfif selectsub_school.recordcount gt 0>
</cfif>
</cfif>
</cfif>
<style type="text/css">
<!--
.style1 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: xx-small;
}
-->
</style>

<p class="style1">
<cfif isdefined("form.searchsub")>
<cfif isdefined("form.subject") and form.subject neq "">
<cfif selectsub_school.recordcount gt 0>
</cfif>
</cfif>
</cfif>
</p>
<cfif isdefined("form.searchsub")><cfif isdefined("form.subject") and form.subject neq ""><cfif selectsub_school.recordcount gt 0><table width="396" border="0" align="left">

<cfoutput>
<div align="justify"><span class="style64"></span><span class="style63"><br>
Your SEARCH for schools that teach <span class="style79 style47"><span class="style78">#subjectName(form.subject)#</span></span> resulted in the following results: </span>
</div>
</cfoutput> </td>

<cfoutput query="selectsub_school">
<td class="boxtext style1"> <div align="left" class="style46">#City# <span class="style76">

<td class="boxtext style1"><div align="left" class="style46">
<div align="right">
<cfif subject_description.SubjectID EQ FORM.Subject >
<cfoutput><span class="style88"><a cfif subject_description.Subject EQ form.Subject AND subject_description.SchoolID EQ selectsub_school.SchoolID >
<cfoutput>
<a href="subject_details.cfm?Subject_DescriptionID=#subject_description.Subject_DescriptionID#" >more info about the #subjectName(form.subject)# program at #School#</a></span></cfoutput>
<cfelse> no description avilable
</cfif>

Hi Dan ignore the previous code...it was the wrong one...

Pete

What you described in the original post is a nicely designed database. What queries run when the form is submitted?

Hi John,
Thanks for the quic reply. YES all the data is in tables in a database.

There is a cfquery running on it, which gives the list of schools when searched. But I just cant seem to be able to place this last bit (subject description). So that the data shows up like this:

IF someone searches for 'Accounting'

School: Robina
City: Robina
more info about the Accounting program at Robina
more info about Robina
search again

OR (if there is no description)

School: Hartford
City: Hatfield
no description found
more info about Hartford
search again

do you know what I mean...?

Peter
.............................................................................................................

Peter,

Is all the infomation in tables, or is it stored in a database, if so you could use a cfquery to interagate the fields and records.

e.g. I have a db that holds a full years training courses (8 types) and each course has it own date, they run for 1, 2,3,4 or 5 days subject to the course.

When someone clicks a course, a list of start dates are shown, if the course has been withdrawn then 'Sorry this course is not ..' I sure you get the picture.

So I would be looking into data dates and using the cfquery tag.

John