Nice! Yes, clarifying the various table instance bindings was the key. Well, that and me not obscuring the issue by over-abstracting the problem.
Thanks much. -glenn On Mon, Aug 4, 2014 at 3:07 PM, Ben Hughes <[email protected]> wrote: > You should bind those subqueries to table aliases or this gets really > confusing. I'll bet something is just not getting bound correctly. > > This is more clear: > > SELECT UserID, Description > FROM dbo.UserCredentials all_credentials > WHERE > EXISTS ( > SELECT * FROM dbo.UserCredentials keypad_credentials > WHERE all_credentials.UserID = keypad_credentials.UserID AND > keypad_credentials.Description = 'Keypad' > ) > AND NOT EXISTS ( > SELECT * FROM dbo.UserCredentials stripe_credentials > WHERE all_credentials.UserID = stripe_credentials.UserID AND > stripe_credentials.Description = 'Magnetic Stripe Card' > ) > > Another thing that is weird is the way you've written that original select > statement: that's not going to return users. It will return > user-description combinations, and since you said you want "users" only, > you should probably just get a list only of the users themselves: > > SELECT DISTINCT all_credentials.UserID > > > > > On Mon, Aug 4, 2014 at 2:46 PM, Glenn Little <[email protected]> wrote: > >> Okay, having some trouble getting [NOT] EXISTS working with my one-table >> example. No more colors etc, here is the actual code. Basically, I have a >> table: >> >> UserCredentials: >> UserID: int >> CredentialNumber: int >> Description: varchar >> >> I want to find the UserID of all users who have a "Keypad" credential, >> but do *not* have a "Magnetic Stripe Card" credential. >> >> Here is my latest try, but unfortunately it returns all rows in the >> table, not just the keypad-only ones: >> >> SELECT UserID as id, Description from dbo.UserCredentials WHERE >> EXISTS ( >> SELECT * FROM dbo.UserCredentials WHERE UserID = id AND Description = >> 'Keypad' >> ) >> AND NOT EXISTS ( >> SELECT * FROM dbo.UserCredentials WHERE UserID = id AND Description = >> 'Magnetic Stripe Card'); >> >> I think I'm getting confused about how to get the "iterating" user id >> into the conditions? >> >> -glenn >> >> -- >> -- >> SD Ruby mailing list >> [email protected] >> http://groups.google.com/group/sdruby >> --- >> You received this message because you are subscribed to the Google Groups >> "SD Ruby" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby > --- > You received this message because you are subscribed to the Google Groups > "SD Ruby" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby --- You received this message because you are subscribed to the Google Groups "SD Ruby" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
