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
Arachn1dAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.