problem from seletion from two tables!!

[Hollywood]

hi everyone i'm havin a problem with selecting data from two tables in a single database. i have a login page that looks like this
login.cfm

<cfform action="index.cfm" method="post">
<cfinput type="text" name="username">
<cfinput type="text" name="password">
<cfinput type="submit" name="submit">
</cfform>

and the index.cfm page looks like this
index.cfm

<cfquery name="qryrDe" datasource="mydsn>
	SELECT UserName, password, surName, middleName
     FROM Users, results
	WHERE UserName = <cfqueryparam value="#trim(form.Username)#" CFSQLType="CF_SQL_VARCHAR">
    AND password = <cfqueryparam value="#trim(form.password)#" CFSQLType="CF_SQL_VARCHAR">
  </cfquery>

it is always throwing an error that the username could be from any of the two tables. please any help will be highly appreciated. thanks

[Gjslick]

Hey Hollywood. First, why do you need to select from two tables here? Is part of the data that you're looking for in one table, and another part in the other table?

If so, can you post the list of column names that are in each of the tables? That would help me in trying to figure out the query that you are trying to perform here.

-Greg

[Jt107]

I did not found any join when select from two tables.

Try this:

<cfquery name="qryrDe" datasource="mydsn>
SELECT UserName, password, surName, middleName
FROM Users u, results r
WHERE u.userid = r.userid
AND UserName = <cfqueryparam value="#trim(form.Username)#" CFSQLType="CF_SQL_VARCHAR">
AND password = <cfqueryparam value="#trim(form.password)#" CFSQLType="CF_SQL_VARCHAR">
</cfquery>