Re: [sqlite] user id select
Thanks Ken, You make it look so simple. Lloyd - Original Message - From: "Williams, Kenneth (Ken) (TLR Corp)" <[EMAIL PROTECTED]> To: "Lloyd thomas" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, April 12, 2004 3:50 PM Subject: RE: [sqlite] user id select > -Original Message- > From: Lloyd thomas [mailto:[EMAIL PROTECTED] > Sent: Sunday, April 11, 2004 1:18 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] user id select > > > I am trying to create a query which selects a user where it > is not part of a > specified group. Perhaps you just want: SELECT user_id FROM users WHERE user_id NOT IN (SELECT user_id FROM grp_user WHERE group_id = 42) -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] user id select
> -Original Message- > From: Lloyd thomas [mailto:[EMAIL PROTECTED] > Sent: Sunday, April 11, 2004 1:18 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] user id select > > > I am trying to create a query which selects a user where it > is not part of a > specified group. Perhaps you just want: SELECT user_id FROM users WHERE user_id NOT IN (SELECT user_id FROM grp_user WHERE group_id = 42) -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] user id select
Thanks chaps, I ended up with the following query, which seems to do the job. Not sure how efficient it is though. sql SELECT U.user_id FROM users U LEFT JOIN grp_user G ON G.user_id = U.user_id WHERE G.group_id != '1'or G.group_id is null; - Original Message - From: "Christian Smith" <[EMAIL PROTECTED]> To: "Lloyd thomas" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, April 11, 2004 9:34 PM Subject: Re: [sqlite] user id select > On Sun, 11 Apr 2004, Lloyd thomas wrote: > > >I have a table with user id's and another with user id's linked to a group. > > > >users table > >|user_id| username | > >|1 | Lloyd Thomas| > >| 2| John Smith | > > > >Group table > >|group id| user_id| > >| 1 | 1 | > > > >I am trying to create a query which selects a user where it is not part of a > >specified group. But it does not seem to work as I get multiple results for > >the same user id's. > > > >sql--- > >Select U.user_id from > >users as U, grp_user as G where U.user_id != G.user_id > > > > You'll get a user_id for each group that the user is not a member off. You > need an extra where clause selecting which group you're checking for: > > Select U.user_id > from users as U, grp_user as G > where U.user_id != G.user_id AND G.group_id != ; > > > > > > > > > >- > >To unsubscribe, e-mail: [EMAIL PROTECTED] > >For additional commands, e-mail: [EMAIL PROTECTED] > > > > -- > /"\ > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] user id select
On Sun, 11 Apr 2004, Lloyd thomas wrote: >I have a table with user id's and another with user id's linked to a group. > >users table >|user_id| username | >|1 | Lloyd Thomas| >| 2| John Smith | > >Group table >|group id| user_id| >| 1 | 1 | > >I am trying to create a query which selects a user where it is not part of a >specified group. But it does not seem to work as I get multiple results for >the same user id's. > >sql--- >Select U.user_id from >users as U, grp_user as G where U.user_id != G.user_id > You'll get a user_id for each group that the user is not a member off. You need an extra where clause selecting which group you're checking for: Select U.user_id from users as U, grp_user as G where U.user_id != G.user_id AND G.group_id != ; > > > >- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]