try changing the formulation to something like
<cfquery>
SELECT tbl_Employee.EmployeeID,
tbl_Employee.Firstname,
tbl_Employee.LastName,
tbl_Employee.Email,
tbl_Employee.EmployeeName
FROM tbl_Employee
WHERE tbl_Employee.EmployeeID NOT IN
(
SELECT DISTINCT EmployeeName
FROM GOLF
)
ORDER BY tbl_Emplyoee.LastName ASC, tbl_Employee.FirstName ASC
</cfquery>
or
<cfquery>
SELECT tbl_Employee.EmployeeID,
tbl_Employee.Firstname,
tbl_Employee.LastName,
tbl_Employee.Email,
tbl_Employee.EmployeeName
FROM tbl_Employee
WHERE NOT EXISTS
(
SELECT EmployeeName
FROM GOLF
WHERE EmployeeName = tbl_Employee.EmployeeID
)
ORDER BY tbl_Employee.LastName ASC, tbl_Employee.FirstName ASC
</cfquery>
Hope that helps
CHeers
Alistair Davidson
Senior Web Developer
Rocom New Media
www.rocomx.net
-----Original Message-----
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: 31 May 2001 15:52
To: CF-Talk
Subject: RE: Select Boxes <> limiting drop down :: Please Look
ok.
still no luck.
i have tried DISTINCT
have tried putting the 2 queries together
i should be able to just have in my sql
WHERE EmployeeName <> EmployeeID
but it doesn't work!!!!!!!!
like below:
<CFQUERY NAME="rs_Employees"
DATASOURCE="golf">
SELECT DISTINCT EmployeeID, FirstName, LastName, Email, EmployeeName
FROM tbl_Employee, GOLF
WHERE EmployeeName <> EmployeeID
ORDER BY LastName
DESC;</CFQUERY>
<!--- The Select Box in Question --->
<select name="Player2">
<cfloop query="rs_Employees">
<option value="<cfoutput>#rs_Employees.EmployeeID#</cfoutput>" <cfif
(#rs_Employees.EmployeeID# EQ
#rs_Employees.EmployeeID#)>SELECTED</cfif>><cfoutput>#rs_Employees.LastName#
#rs_Employees.FirstName#</cfoutput></option>
</cfloop>
</select>
But then i get all sorts of duplicates.
-paul
"Whatever you can do, or dream you can, begin it.
Boldness has genius, power, and magic in it." - Goethe
web developer, nbbj
work: [EMAIL PROTECTED]
614 241-3534
fax: 614 485-5534
home: [EMAIL PROTECTED]
614 449-1681
> icq: 47658358
-----Original Message-----
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 9:13 AM
To: CF-Talk
Subject: Select Boxes <> limiting drop down :: Brian Lock
Ok i know this is simple but i have forgot something
i want to limit the returned names on a select box to
only those who have not signed up!
<!---
Get the id of golfer EmployeeName
[which is an ID] for limiting the drop down
--->
<cfquery name="Get_Golf" datasource="golf">
SELECT GolfID, EmployeeName
FROM tbl_Golf
</cfquery>
<!---
Find Employee Name for drop down ::
But Hiding employees who have already signed up,
with the where statement
--->
<cfquery name="rs_Employees" datasource="golf">
SELECT EmployeeID, FirstName, LastName, Email
FROM tbl_Employee
WHERE #Get_Golf.EmployeeName# <> .EmployeeID
ORDER BY LastName
DESC;
</cfquery>
<!--- The Select Box in Question --->
<select name="Player2">
<cfloop query="rs_Employees">
<option value="<cfoutput>#rs_Employees.EmployeeID#</cfoutput>" <cfif
(#rs_Employees.EmployeeID# EQ
#rs_Employees.EmployeeID#)>SELECTED</cfif>><cfoutput>#rs_Employees.LastName#
#rs_Employees.FirstName#</cfoutput></option>
</cfloop>
</select>
Thank You!
-paul
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
