• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

Table Join not working

I am trying to set up a simple table join. The code I am using is below:

<cfquery name="SelectedPeople" datasource="DatabaseTest1" dbtype="ODBC">
  SELECT Main.Name, Main.Address, Main.Phone, Main.Wonderful, Groups.Name
  FROM Main, Groups
  WHERE      Main.Name like '%#form.TheirName#%'
    AND  Main.GroupCode = Groups.GroupCode
</cfquery>

<cfif SelectedPeople.RecordCount is "0">
  No results matched your search criteria
</cfif>
<cfoutput query="SelectedPeople">
  Name: #Main.Name#<br>
  Address: #Main.Address#<br>
  Phone: #Main.Phone#<br>
  Group: #Groups.Name#<br>
  But are they wonderful?
  <CFIF #Main.Wonderful# is 1>
    Yes!
  <cfelse>
    No!
  </cfif><br><br>
</cfoutput>

When I run this, coldfusion reports:
Error resolving MAIN.NAME
ColdFusion was unable to determine the value of the parameter

I have set up the database source, and tried it successfully without the extra Groups table, and it has run fine, and when I submit the form that feeds into this with the TheirName field set to nothing, it returns all three records. Both tables have records, and all the records in the Main table have a record in the Groups table with the same ID. Also, when I try getting the RecordCount for this query, it returns three. So what am I doing wrong?

Nick

P.S I know the fields look ridiculous, but this is just a practice exercise, to learn ColdFusion
0
Arachn1d
Asked:
Arachn1d
  • 2
1 Solution
 
FRehmanCommented:
You use this code
hope this will help you
<cfquery name="SelectedPeople" datasource="DatabaseTest1" dbtype="ODBC">
SELECT Main.Name, Main.Address, Main.Phone, Main.Wonderful, Groups.Name
FROM Main, Groups
WHERE Main.Name like '%#form.TheirName#%'
AND  Main.GroupCode = Groups.GroupCode
</cfquery>
<cfif SelectedPeople.RecordCount is "0">
<cfoutput>No results matched your search criteria </cfoutput>
<cfelse>
<cfoutput query="SelectedPeople">
  Name: #Main.Name#<br>
  Address: #Main.Address#<br>
  Phone: #Main.Phone#<br>
  Group: #Groups.Name#<br>
  But are they wonderful?
  <CFIF #Main.Wonderful# is 1>
    Yes!
  <cfelse>
    No!
  </cfif><br><br>
</cfoutput>
</cfif>
if you find any problem then let me know at
leo_faisal@yahoo.com
0
 
Arachn1dAuthor Commented:
I still have the same problem - the problem is not when there are no results returned, only when there is more than one - it complains that it was "Unable to determine the value of the paramater MAIN.NAME". This same query works fine without the table join, and it does return 3 records even when joined.
0
 
meverestCommented:
try this:

<cfquery name="SelectedPeople" datasource="DatabaseTest1" dbtype="ODBC">
  SELECT Main.Name as name, Main.Address as address, Main.Phone as phone, Main.Wonderful as wonderful, Groups.Name as groupname
  FROM Main, Groups
  WHERE Main.Name like '%#form.TheirName#%'
    AND  Main.GroupCode = Groups.GroupCode
</cfquery>

<cfif SelectedPeople.RecordCount is "0">
  No results matched your search criteria
</cfif>
<cfoutput query="SelectedPeople">
  Name: #SelectedPeople.Name#<br>
  Address: #SelectedPeople.Address#<br>
  Phone: #SelectedPeople.Phone#<br>
  Group: #SelectedPeople.groupname#<br>
  But are they wonderful?
  <CFIF #Main.Wonderful# is 1>
    Yes!
  <cfelse>
    No!
  </cfif><br><br>
</cfoutput>

regards.


0
 
Arachn1dAuthor Commented:
Thanks - Theres nothing better than a solution that works. Pleas note tho, anyone else reading this that:
    <CFIF #Main.Wonderful# is 1>
should be
    <CFIF #SelectedPeople.Wonderful# is 1>

Also, the SelectedPeople prefix is not neccessary.

Nick
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now