If this is a double post I appologize, my mail server is acting up and I
think it just cacked on the send queue again.
Not using outer join, but this will do the trick.
SELECT DISTINCT g.groupname
FROM grouptable g
WHERE g.groupID NOT IN (
SELECT m.groupID
FROM groupusertable m
WHERE m.UserID = #userID#
)
Not sure the the performance costs versus doing it through a join.
-----Original Message-----
From: Jeffry Houser [mailto:[EMAIL PROTECTED]]
Sent: January 22, 2001 14:30
To: CF-Talk
Subject: Outer Join Question...
I'm stuck:
The tables:
grouptable (groupID, groupname)
usertable (userID, otheruserinformation)
groupusertable (groupID, userID)
Given the userID, how do I get a list of groups that the user is not in?
This will return all the groups that the user is in:
SELECT DISTINCT grouptable.groupID, grouptable.groupname
FROM grouptable LEFT OUTER JOIN groupusertable
ON (grouptable.groupID = groupusertable.groupID) and
(groupusertable.userID = #userID#)
This will return all groups the user is not in, but some other user
is:
SELECT DISTINCT grouptable.groupID, grouptable.groupname
FROM grouptable LEFT OUTER JOIN groupusertable
ON (grouptable.groupID = groupusertable.groupID) and
not (groupusertable.userID = #userID#)
Any help, would be appreciated. Thanks
--
Jeff Houser | mailto:[EMAIL PROTECTED]
AIM: Reboog711 | ICQ: 5246969 | Phone: 860-229-2781
--
Instant ColdFusion 4.5 | ISBN: 0-07-213238-8
Due out 3rd Quarter 2001
--
DotComIt, LLC
database driven web data using ColdFusion, Lotus Notes/Domino
--
Half of the Alternative Folk Duo called Far Cry Fly
http://www.farcryfly.com | http://www.mp3.com/FarCryFly
--
Promise me no dead end streets, and I'll guarantee we'll have a road
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
