Select .. IN query needs a little help

Posted on 2005-04-28
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

    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)
    LVL 1

    Author Comment

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

    Expert Comment

    by:Racim BOUDJAKDJI

    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 Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now