1 Reply Latest reply on May 18, 2006 8:18 PM by Dan Bracuk

    Verity Query of Query

    PDorian
      I have been trying to JOIN my verity results with database table but I can not seem to accomplish this. I keep getting the error Query Of Queries syntax error.
      Encountered "INNER.

      I have included all my code below. I have looked at all the other postings regarding this and matched my SQL the same way. I can not seem to get this to work. Any help would be appreciated.

      <cfparam name="FORM.Searchtext" default="">

      <FORM action="<cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" method="post" name="search">

      <table width="90%" border="1" cellpadding="1" cellspacing="0" class="unameit">
      <thead>
      <tr>
      <td colspan="2"><div align="center">Search Issues</div></td>
      </tr>
      </thead>
      <tr>
      <td><div align="right">Search</div></td>
      <td><input name="searchtext" type="text" class="input" value="<cfoutput>#FORM.Searchtext#</cfoutput>" size="50"></td>
      </tr>
      <tr>
      <td> </td>
      <td><input name="Search" type="submit" id="Search" value="Search"></td>
      </tr>
      </table>
      </form>
      <p> </p>
      <p>
      <cfif FORM.Searchtext NEQ "">
      <cfsearch
      name="mysearch"
      collection="tickets" type="simple"
      criteria="#FORM.Searchtext#"
      contextpassages = "3">


      <cfquery name="getIssueList" datasource="unameitsweb" dbtype="query">
      SELECT *
      FROM dbo.IssueList
      </cfquery>

      <cfquery name="JoinedSearch" dbtype="query">
      SELECT getIssueList.TaskActivityID, mysearch.Score
      FROM getIssueList INNER JOIN mysearch on getIssueList.TaskActivityID = mysearch.[key]
      ORDER BY mySearch.Score
      </cfquery>


      <cfoutput>Search Result total = #JoinedSearch.RecordCount# </cfoutput><br>
      <cfoutput query="JoinedSearch" startrow="1"
      maxrows="100">
      <strong>Title: <a href="IssueEntry.cfm?action=view&id=#key#">#title#</a></a></strong><br>
      <strong>Score:</strong> #score#<br>
      <strong>Summary:</strong> #search.context#<br>
      #CurrentRow# out of #RecordCount# found<br><br>
      </cfoutput>
      </cfif>