Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
abhinav.jain09
Starting Member
2 Posts |
Posted - 2009-09-08 : 08:06:23
|
Hello friendsI want to write a query according to following Table is Name GroupAbhinav Group1Rahul Group2Abhinav Group2Ram Group1Shayam Group4Rahul Group1Tom Group1Kim Group2Nitin Group7In the above table both the column can have repeated value.Question Select all names from the table that belongs to the Group1 & Group2 Selection of Group will be from the screen and user can select any number of group in this example user has selected only two group (Group1 and Group2). This is variable selection not a constant.Output will be the Abhinav and Rahul. Tom and Kim should not be in the output since tom belong only Group1 not in Group2. Kim belongs to only Group 2 not in Group1.Thanks |
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-09-08 : 08:29:43
|
select name,count(name) from ttable group by namehaving count(name)>1Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
ra.shinde
Posting Yak Master
103 Posts |
Posted - 2009-09-08 : 08:55:55
|
DECLARE @TAB TABLE(Name VARCHAR(10),[GROUP] VARCHAR(10))INSERT INTO @TABSELECT 'Abhinav','Group1'UNION ALL SELECT 'Rahul','Group2'UNION ALL SELECT 'Abhinav','Group2'UNION ALL SELECT 'Ram','Group1'UNION ALL SELECT 'Shayam','Group4'UNION ALL SELECT 'Rahul','Group1'UNION ALL SELECT 'Tom','Group1'UNION ALL SELECT 'Kim','Group2'UNION ALL SELECT 'Nitin','Group7'DECLARE @grp VARCHAR(50);SET @grp = 'Group1,Group2';DECLARE @GTAB TABLE ([GROUP] VARCHAR(10));WITH TAB(ID) AS( Select 1 UNION ALL SELECT ID+1 FROM TAB where ID < 8000)INSERT INTO @GTABSELECT REPLACE(G,'''','') AS G FROM (SELECT SubString(','+@grp+',', ID, CHARINDEX(',',','+@grp+',',ID)-ID) AS G FROM TABWHERE ID <= Len(',' + @grp + ',') AND SubString(',' + @grp + ',' , ID - 1, 1) = ','AND CharIndex(',' , ',' + @grp + ',' , ID) - ID > 0 --remove this line to keep NULL rows)AOPTION (MAXRECURSION 8000);SELECT Name FROM @TAB T INNER JOIN @GTAB GT ON T.[GROUP] = GT.[GROUP]GROUP BY T.NameHAVING count(*) = (SELECT count(*) FROM @GTAB)Rahul Shinde |
|
|
abhinav.jain09
Starting Member
2 Posts |
Posted - 2009-09-09 : 00:54:50
|
Thanks your response so far. To be more clear on my questions below is an example.group1AbhinavRamRahulTomGroup2RahulAbhinavKimGroup4ShyamGroup7NitinThe above shows the users according to the group. The group is a multiselect list box. The user selects groups and clicks go buttonScenario1: User select group1Expected o/p: AbhinavRamRahulTomScenario2: User select group2Expected o/p: RahulAbhinavKimScenario3: User select group1 and group2Expected o/p: RahulAbhinavIn the above output Ram and Tom should not be include since the are not present in both the groups.Similary the user can select any no of groups.Users in each group keeps increasing.Thanks |
|
|
ra.shinde
Posting Yak Master
103 Posts |
Posted - 2009-09-09 : 00:58:18
|
Did you tried SQL in my post?Rahul Shinde |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-09-09 : 04:48:58
|
[code]DECLARE @sample TABLE( [Name] varchar(10), [Group] varchar(10))INSERT INTO @sampleSELECT 'Abhinav', 'Group1' UNION ALL SELECT 'Rahul', 'Group2' UNION ALL SELECT 'Abhinav', 'Group2' UNION ALL SELECT 'Ram', 'Group1' UNION ALL SELECT 'Shayam', 'Group4' UNION ALL SELECT 'Rahul', 'Group1' UNION ALL SELECT 'Tom', 'Group1' UNION ALL SELECT 'Kim', 'Group2' UNION ALL SELECT 'Nitin', 'Group7'DECLARE @groups varchar(100), @grp_cnt intSELECT @groups = 'Group1,Group2'SELECT @grp_cnt = LEN(@groups) - LEN(REPLACE(@groups, ',', '')) + 1SELECT s.[Name]FROM @sample s INNER JOIN CSVTable(@groups) g ON s.[Group] = g.stringvalGROUP BY s.[Name]HAVING COUNT(*) = @grp_cnt/*Name ---------- AbhinavRahul(2 row(s) affected)*/[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
gopalrwt77
Starting Member
3 Posts |
Posted - 2014-10-15 : 07:58:07
|
select distinct Name from table1 where group in('group1','group2')gopal |
|
|
aniruddhaa
Starting Member
3 Posts |
Posted - 2014-10-16 : 06:59:36
|
SELECT X.name FROM (SELECT name,Grp from Trick_Query_DS where Grp='GROUP 1') X INNER JOIN (SELECT name,Grp from Trick_Query_DS where Grp='GROUP 2') Y ON X.Name=Y.NameBest,Aniruddha |
|
|
|
|
|
|
|