Select .. IN query needs a little help

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])
LVL 1
mcsolasAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
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)
0
 
mcsolasAuthor Commented:
Both work! Much thanks for the quick reply.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Try...

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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.