Select list of people in table with multiple rows having value equal to a x, y, z

I have a person-group table.

It lists all the groups that a person is part of. So there can be multiple persons in the list for each group they are part of.

For example:

JOHN SMITH GROUPA
JOHN SMITH GROUPB
JANE SMITH GROUPC
JANE SMITH GROUPB
JANE SMITH GROUPA
JACK APPLE GROUPD
...

How do I select from that all the people that are part of GROUPA and GROUPB?

The solution seems to be escaping me.
intoxicated_curveballAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try

select distinct( nameColumn) from tbl where groupColumnName = 'GROUPA' or groupColumnName = 'GROUPB'
0
Lee SavidgeCommented:
I assume that the names are in one or two columns and the groups are in another?

select * from myTable where groupName in ('groupa', 'groupb')

Open in new window


Or am I missing something?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
If the "group" is in it's own column then:

select * from <table>
where <column> in ('GROUPA', 'GROUPB')


if that's all one column then take the right 6 chars:

select * from <table>
where right(<column>, 6) in ('GROUPA', 'GROUPB')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
SELECT         Name
FROM           MyTable
GROUP BY   Name
HAVING        (Groups IN ('GROUPA', 'GROUPB'))
0
intoxicated_curveballAuthor Commented:
Sorry the only stipulation is I need it to return the DISTINCT names having BOTH groups

If I use

select * from myTable where groupName in ('groupa', 'groupb')

Open in new window


Seems to returns even if person is only in groupA.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Actually...

SELECT         Name
FROM           MyTable
GROUP BY   Name
HAVING        Groups='GROUPA' AND Groups = 'GROUPB'
0
intoxicated_curveballAuthor Commented:
Hi Scott, seems you might be on to it... but when I try what you said it says GROUP must be in a AGGREGATE function or GROUP BY clause and I can't really GROUP BY this can I?
0
intoxicated_curveballAuthor Commented:
No I don't think HAVING is right as it's only used for AGGREGATE functions...
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
did the solution I posted not work? if not what results did you get and what were you expecting to get?
0
intoxicated_curveballAuthor Commented:
Kyle and others, If I use

select * from <table>
where <column> in ('GROUPA', 'GROUPB')

It returns people even if they aren't in both groups (only in at least one).  They have to be in both.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
CREATE TABLE Contacts 
	(
    
     name varchar(20), 
     groups varchar(30)
    );
 
INSERT INTO Contacts
(name, groups)
VALUES
('mike', 'one'),
('mike', 'two'),
('jill', 'three'),
('jill', 'one'),
('john', 'one'),
('john', 'two');

Open in new window


SELECT         name
FROM           Contacts
WHERE          groups ='one' AND groups = 'two'
GROUP BY       name

Open in new window

0
intoxicated_curveballAuthor Commented:
Big Monty

Your solution would also return those in either group A and group B, but they need to be in BOTH groups.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
just change the OR to AND and it should give you what you want
0
intoxicated_curveballAuthor Commented:
Big Monty, AND doesn't work and wouldn't work because a row can't be both groups, so it will always return zero results.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
The last solution I provided actually will not work if you have multiple rows (one row per group/name).  You would have to relate the table back to itself.

Can you give a sample layout of the table(s)
0
intoxicated_curveballAuthor Commented:
Scott my original post has the sample data, there are two columns, one for NAME and one for GROUP. I have thousands of records in my table.
0
intoxicated_curveballAuthor Commented:
There is a convoluted way to do this but I find it hard to believe there's not a really simple solution. The convoluted way:

SELECT NAME FROM TABLE WHERE NAME IN 
(SELECT NAME FROM TABLE WHERE GROUP = GROUPA) 
WHERE GROUP = GROUPB

Open in new window


And the problem is I have more then two groups that actually need to be selected so it ends up being something more like

SELECT NAME FROM 
(SELECT NAME FROM TABLE WHERE NAME IN 
(SELECT NAME FROM TABLE WHERE GROUP = GROUPA) 
WHERE GROUP = GROUPB) 
WHERE GROUP = GROUPC

Open in new window



etc.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
;with cte as (
select name. group from
<table>
where group in ('groupA', 'GroupB')
)

select name from cte
group by name
having count(*) >= 2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
intoxicated_curveballAuthor Commented:
Thank you!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.