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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Brain twisting query

Author  Topic 

abhinav.jain09
Starting Member

2 Posts

Posted - 2009-09-08 : 08:06:23
Hello friends
I want to write a query according to following
Table is

Name Group
Abhinav Group1
Rahul Group2
Abhinav Group2
Ram Group1
Shayam Group4
Rahul Group1
Tom Group1
Kim Group2
Nitin Group7

In 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 name
having count(name)>1

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/
Go to Top of Page

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 @TAB
SELECT '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 @GTAB
SELECT REPLACE(G,'''','') AS G FROM
(
SELECT SubString(','+@grp+',', ID, CHARINDEX(',',','+@grp+',',ID)-ID) AS G
FROM TAB
WHERE ID <= Len(',' + @grp + ',')
AND SubString(',' + @grp + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + @grp + ',' , ID) - ID > 0 --remove this line to keep NULL rows
)A

OPTION (MAXRECURSION 8000);

SELECT Name
FROM @TAB T INNER JOIN @GTAB GT ON T.[GROUP] = GT.[GROUP]
GROUP BY T.Name
HAVING count(*) = (SELECT count(*) FROM @GTAB)






Rahul Shinde
Go to Top of Page

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.

group1

Abhinav
Ram
Rahul
Tom


Group2
Rahul
Abhinav
Kim

Group4
Shyam

Group7
Nitin

The above shows the users according to the group. The group is a multiselect list box. The user selects groups and clicks go button

Scenario1: User select group1
Expected o/p:

Abhinav
Ram
Rahul
Tom


Scenario2: User select group2
Expected o/p:

Rahul
Abhinav
Kim


Scenario3: User select group1 and group2
Expected o/p:

Rahul
Abhinav

In 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
Go to Top of Page

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-09 : 00:58:18
Did you tried SQL in my post?

Rahul Shinde
Go to Top of Page

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 @sample
SELECT '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 int

SELECT @groups = 'Group1,Group2'
SELECT @grp_cnt = LEN(@groups) - LEN(REPLACE(@groups, ',', '')) + 1

SELECT s.[Name]
FROM @sample s
INNER JOIN CSVTable(@groups) g ON s.[Group] = g.stringval
GROUP BY s.[Name]
HAVING COUNT(*) = @grp_cnt

/*
Name
----------
Abhinav
Rahul

(2 row(s) affected)
*/

[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

gopalrwt77
Starting Member

3 Posts

Posted - 2014-10-15 : 07:58:07
select distinct Name from table1
where group in('group1','group2')

gopal
Go to Top of Page

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.Name

Best,
Aniruddha
Go to Top of Page
   

- Advertisement -