Help On Query

  • create table Sample

    (Name Varchar(100),

    Role Varchar(10))

    insert into Sample values ('Vignesh' , 'Admin')

    insert into Sample values ('Vignesh' , 'User')

    insert into Sample values ('Bala' , 'Admin')

    insert into Sample values ('Bala' , 'User')

    insert into Sample values ('Suresh' , 'Admin')

    insert into Sample values ('Arun' , 'User')

    1. In sample table there were 4 names Vignesh, Bala, arun & suresh

    2. There are 2 kinds of role (admin & user)

    3. Vignesh & bala have both the roles , arun & suresh have any one of the role

    I need to find who are having both roles ..

    Kindly help..

  • This should do the trick:

    SELECT Name

    FROM SAMPLE

    GROUP BY Name

    HAVING COUNT(*) > 1;

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen's query is much easier, but here's my alternate query anyway.

    WITH cteRoles

    AS

    (

    SELECTROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,

    Name,

    Role

    FROMSample

    )

    SELECTName

    FROMcteRoles

    WHERERowNr = 2



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • Double post after error.



    For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

    "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

  • If there are double rows (like the same NAME is entered twice both with the ROLE 'User') the solution of Koen will give false results. The code below will just display the results where a NAME is entered only once as 'Admin' and once as 'User'.

    select name

    from Sample

    group by name

    having sum(case when ROLE = 'Admin' then 1 else 0 end) = 1

    and sum(case when ROLE = 'User' then 1 else 0 end) = 1

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • HanShi (9/10/2013)


    If there are double rows (like the same NAME is entered twice both with the ROLE 'User') the solution of Koen will give false results. The code below will just display the results where a NAME is entered only once as 'Admin' and once as 'User'.

    select name

    from Sample

    group by name

    having sum(case when ROLE = 'Admin' then 1 else 0 end) = 1

    and sum(case when ROLE = 'User' then 1 else 0 end) = 1

    Pffff, crap in crap out 😀

    I believe this to be a more elegant solution:

    SELECT Name

    FROM [SAMPLE]

    GROUP BY Name

    HAVING COUNT(DISTINCT [Role]) > 1;

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (9/10/2013)


    Pffff, crap in crap out 😀

    I totally agree!! 😎

    But with such a limited sample you better prepare for the worst. Or at least provide different solutions. The OP can pick the one that fits the best. And he's the only one who can decide which one that is.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • +1 to koen ... 🙂

  • drop table #Sample

    create table #Sample(Name Varchar(100),Role Varchar(10))

    insert into #Sample values ('Vignesh' , 'Admin')

    insert into #Sample values ('Vignesh' , 'User')

    insert into #Sample values ('Bala' , 'Admin')

    insert into #Sample values ('Bala' , 'User')

    insert into #Sample values ('Suresh' , 'Admin')

    insert into #Sample values ('Arun' , 'User')

    insert into #Sample values ('Suresh' , 'Admin')

    insert into #Sample values ('Arun' , 'User')

    -- user has two or more rows in the table - may be same role

    SELECT Name

    FROM #SAMPLE

    GROUP BY Name

    HAVING COUNT(*) > 1;

    -- user has two or more rows in the table - may be same role

    WITH cteRoles

    AS

    (

    SELECTROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,

    Name,

    Role

    FROM#Sample

    )

    SELECTName

    FROMcteRoles

    WHERERowNr = 2

    -- user has any two or more roles

    SELECT s.Name

    FROM #Sample s

    WHERE EXISTS (

    SELECT 1

    FROM #Sample i

    WHERE i.Name = s.Name

    AND i.[Role] <> s.[Role]

    )

    -- user has two or more roles, including both Admin and User

    SELECT Name

    FROM (

    SELECT Name, [Role]

    FROM #Sample s

    WHERE [Role] IN ('Admin','User')

    GROUP BY Name, [Role]

    ) d

    GROUP BY Name

    HAVING COUNT(*) > 1

    -- user has two or more roles, including both Admin and User

    SELECT s.Name

    FROM #Sample s

    WHERE EXISTS (

    SELECT 1

    FROM #Sample i

    WHERE i.Name = s.Name

    AND i.[Role] = 'User'

    )

    AND s.[Role] = 'Admin'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks everybody...

    It helps me a lot .

  • For those using ROW_NUMBER, if you want users having 2 OR MORE roles, shouldn't the filter be rownum >= 2 instead of just rownum = 2?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • To avoid the duplicate results use rank().

    WITH CTEROLES

    AS(

    SELECT Name,Role,RANK() OVER( PARTITION BY Name ORDER BY Role) AS NumberofRoles FROM Sample

    )

    SELECT Name,NumberofRoles FROM CTEROLES WHERE NumberofRoles>1

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply