dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] Sql query to find records with this criteria

  1. #1

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,180

    Resolved [RESOLVED] Sql query to find records with this criteria

    Hi,

    In the database I have tblUsers

    I can see that:

    • Mary works in Area A and also Mary is an "Admin"
    • Peter also works in Area A but he is only "User"


    Name:  1.JPG
Views: 125
Size:  18.1 KB


    If I look at Area B I can see that there is no Admin... Just two "users" (Paul and Jane)

    I need to find all the Areas that does not have an Admin...

    Is this possible?

    Thanks!!

    If I say:

    Code:
    SELECT  Area FROM TblUsers 
     WHERE UserType <> 'Admin'
    It will show all the users... that is not Admin... right... not helping... I somehow need to group it by Area and what if there is an Admin in that area then I just excluded them with my WHERE clause

    Well... actually, I have no idea
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,359

    Re: Sql query to find records with this criteria

    Here's one way:
    Code:
    SELECT Area 
    FROM TblUsers 
    WHERE Area NOT IN (
                 SELECT Area 
                 FROM TblUsers 
                 WHERE UserType = 'Admin'
                 )
    (build a list of Area's that do have an admin, then get all Area's that aren't in that list)

  3. #3

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,180

    Re: Sql query to find records with this criteria

    Quote Originally Posted by si_the_geek View Post
    Here's one way:
    Code:
    SELECT Area 
    FROM TblUsers 
    WHERE Area NOT IN (
                 SELECT Area 
                 FROM TblUsers 
                 WHERE UserType = 'Admin'
                 )
    (build a list of Area's that do have an admin, then get all Area's that aren't in that list)
    wow!! I did not think it is going to be possible. I was trying for long today to get this

    Thank you very much Si

    I just add the GROUP BY at the bottom and it is perfect.

    I wonder if I will ever get there where some people on vbforums are.. I still have so much to learn
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  4. #4

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,180

    Re: Sql query to find records with this criteria

    Its like one query on top of another. I understand what you did... Genius!
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,502

    Re: [RESOLVED] Sql query to find records with this criteria

    Another approach:-
    Code:
    Select Distinct Area
    From tbleUsers
    
    Except
    
    Select Area
    From tblUsers
    Where UserType = 'Admin'
    I really like Excepts for their clarity
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    Frenzied Member schoemr's Avatar
    Join Date
    Apr 2016
    Location
    South Africa
    Posts
    1,180

    Re: [RESOLVED] Sql query to find records with this criteria

    Thank you very much Funky
    Don't miss the whole point of the dance...

    https://www.youtube.com/watch?v=qHnIJeE3LAI

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width