Re: Outer Join Question...

2001-01-22 Thread Jeffry Houser 



"Raymond B." wrote:

> Not using outer join, but this will do the trick.
>
> SELECT  DISTINCT g.groupname
> FROMgrouptable g
> WHERE   g.groupID NOT IN (
> SELECT  m.groupID
> FROMgroupusertable m
> WHERE   m.UserID = #userID#
> )
>

  Thanks all for the responses.

  This did it.

--
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

RE: Outer Join Question...

2001-01-22 Thread Raymond B. 

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
FROMgrouptable g
WHERE   g.groupID NOT IN (
SELECT  m.groupID
FROMgroupusertable 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

RE: Outer Join Question...

2001-01-22 Thread Raymond B. 

Not using outer join, but this will do the trick.

SELECT  DISTINCT g.groupname
FROMgrouptable g
WHERE   g.groupID NOT IN (
SELECT  m.groupID
FROMgroupusertable 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

RE: Outer Join Question...

2001-01-22 Thread Hayes, David 

How about

Select groupID, groupName
from grouptable
where groupID NOT IN 
(select groupID from groupUserTable where userID = #userID#)

?


-Original Message-
From: Jeffry Houser [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 22, 2001 1:30 PM
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

RE: Outer Join Question...

2001-01-22 Thread DeVoil, Nick 

It's not an outer join in fact.

select groupID, groupname from grouptable
where groupID not in
(select groupID from groupusertable where userID = #userID#)

Nick

-Original Message-
From: Jeffry Houser [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 22, 2001 7:30 PM
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