[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Select .. IN query needs a little help

Posted on 2005-04-28
Medium Priority
Last Modified: 2010-03-19
Platform: sql2000

I am trying to build a query where we are looking for people who have not yet logged in. The users are tied to a master group entity, so there is 3 tables involved here.

Group owns child Providers, only the Providers can have logins. So, we are trying to lookup, by parent ProviderGroup, which child logins have not visited our system yet. Right now the syntax of the query isn't quite right ( maybe the logic is wrong .. but I think just the syntax )

SELECT     Users.LoginSuccess
FROM         Users
WHERE     ((Users.LoginSuccess > 0) AND (Users.UserTable = 'Provider') AND (Users.UserTableID = Provider.ProviderID)) IN
                          (SELECT     Provider.ProviderID
                            FROM          Provider
                            WHERE      Provider.ProviderGroupID = [536])
Question by:mcsolas
LVL 28

Accepted Solution

rafrancisco earned 2000 total points
ID: 13886822
Try this:

SELECT     Users.LoginSuccess
FROM         Users
WHERE     (Users.LoginSuccess > 0) AND (Users.UserTable = 'Provider') AND
                Users.UserTableID IN (SELECT     Provider.ProviderID
                                                 FROM          Provider
                                                 WHERE      Provider.ProviderGroupID = 536)

or this:

SELECT     Users.LoginSuccess
FROM         Users
WHERE     (Users.LoginSuccess > 0) AND (Users.UserTable = 'Provider') AND
                EXISTS (SELECT 'X' FROM Provider WHERE Provider.ProviderGroupID = 536 AND
                                                                               Provider.ProviderID = Users.UserTableID)

Author Comment

ID: 13886956
Both work! Much thanks for the quick reply.
LVL 23

Expert Comment

ID: 13887044

select     u.LoginSuccess from Users u inner join Provider p on u.UserTableID = p.ProviderID
where u.LoginSuccess > 0 and u.UserTable = 'Provider' and p.ProviderGroupID = 536

Hope this helps...

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question