this is what works. but i obviously need to output the listing id's
dynamically.
any ideas?
<!--- Query to get all employees not assigned to app. --->
<cfquery name="qryEmp" datasource="ITData">
SELECT tblAppIAUsers.AppID,
tblAppIAUsers.Listing_ID,
qryIAPeople.LISTING_ID,
qryIAPeople.LAST_NM,
qryIAPeople.MIDDLE_NM,
qryIAPeople.FIRST_NM,
qryIAPeople.SUFFIX,
qryIAPeople.CITY,
qryIAPeople.LABEL,
qryIAPeople.PHONE_NUM,
qryIAPeople.PHONE_TYP_ID,
qryIAPeople.CLASSIFICATION_ID,
qryIAPeople.INTERNAL_ADDR,
qryIAPeople.OWN_DIR_ID,
qryIAPeople.OWN_LIST_ID
FROM qryIAPeople LEFT JOIN tblAppIAUsers ON qryIAPeople.LISTING_ID =
tblAppIAUsers.Listing_ID
WHERE qryIAPeople.Listing_ID <> 105655 AND qryIAPeople.Listing_ID
<>105683
ORDER BY qryIAPeople.Last_NM
</cfquery>
<!--- End Query --->
-----Original Message-----
From: Ben Forta [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 20, 2001 4:50 PM
To: CF-Talk
Subject: RE: query headache
Becky,
Without your schemas I can't give you the exact syntax, but ...
Use NOT IN, kind of like this:
SELECT people
FROM table
WHERE NOT userid IN (SELECT assigned userids)
--- Ben
-----Original Message-----
From: Jones, Becky [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 20, 2001 4:42 PM
To: CF-Talk
Subject: query headache
hey,
i am trying to write a query where i would get an output of all employees
except for those that are already assigned to the chosen
application. this works as far as it doesnt show me the users
assigned....but it doesnt give me a complete listing of everyone else.
for example, say i chose office 2000. and i have joe and tim assigned to
it. well, this query doesnt bring up joe or tim. that's fine.
but it doesnt give me everyone else. like if say sue is assigned to another
application...she wont show up in this list even though she isnt assigned to
office 2000. get it?
i should probably post this to a sql or access group...but maybe someone out
here can help.
thanks,
bec.
<!--- Query to get all employees not assigned to app. --->
<cfquery name="qryEmp" datasource="ITData">
SELECT qryIAPeople.*,
tblAppIAUsers.AppID,
tblAppIAUsers.Listing_ID
FROM qryIAPeople LEFT JOIN tblAppIAUsers ON
qryIAPeople.LISTING_ID = tblAppIAUsers.Listing_ID
WHERE (((tblAppIAUsers.AppID) Is Null Or
(tblAppIAUsers.AppID)=#url.aid#)
AND ((tblAppIAUsers.Listing_ID) Is Null))
ORDER BY qryIAPeople.Last_NM
</cfquery>
<!--- End Query --->
*************************************************
This e-mail, including any attachments, is intended for the
receipt and use by the intended addressee(s), and may contain
confidential and privileged information. If you are not an intended
recipient of this e-mail, you are hereby notified that any unauthorized
use or distribution of this e-mail is strictly prohibited.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
