Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help On Query Expand / Collapse
Author
Message
Posted Tuesday, September 10, 2013 12:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:28 AM
Points: 138, Visits: 382
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..
Post #1493002
Posted Tuesday, September 10, 2013 12:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 13,568, Visits: 10,445
This should do the trick:

SELECT Name
FROM SAMPLE
GROUP BY Name
HAVING COUNT(*) > 1;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1493010
Posted Tuesday, September 10, 2013 1:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
Koen's query is much easier, but here's my alternate query anyway.

WITH cteRoles
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,
Name,
Role
FROM Sample
)
SELECT Name
FROM cteRoles
WHERE RowNr = 2





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

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1493017
Posted Tuesday, September 10, 2013 1:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
Double post after error.



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

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1493018
Posted Tuesday, September 10, 2013 1:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,255, Visits: 2,718
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’! **
Post #1493025
Posted Tuesday, September 10, 2013 1:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 13,568, Visits: 10,445
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;





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1493027
Posted Tuesday, September 10, 2013 1:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 2,255, Visits: 2,718
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’! **
Post #1493030
Posted Tuesday, September 10, 2013 2:13 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 8:01 AM
Points: 688, Visits: 1,234
+1 to koen ...
Post #1493035
Posted Tuesday, September 10, 2013 2:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 7,187, Visits: 13,641
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
(
SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,
Name,
Role
FROM #Sample
)
SELECT Name
FROM cteRoles
WHERE RowNr = 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
Exploring Recursive CTEs by Example Dwain Camps
Post #1493039
Posted Tuesday, September 10, 2013 3:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:28 AM
Points: 138, Visits: 382
Thanks everybody...

It helps me a lot .
Post #1493064
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse