Solved

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

Posted on 2014-10-22
19
163 Views
Last Modified: 2014-10-22
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.
0
Comment
Question by:intoxicated_curveball
  • 9
  • 4
  • 3
  • +2
19 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 40397311
try

select distinct( nameColumn) from tbl where groupColumnName = 'GROUPA' or groupColumnName = 'GROUPB'
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40397315
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40397317
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40397340
SELECT         Name
FROM           MyTable
GROUP BY   Name
HAVING        (Groups IN ('GROUPA', 'GROUPB'))
0
 

Author Comment

by:intoxicated_curveball
ID: 40397346
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40397351
Actually...

SELECT         Name
FROM           MyTable
GROUP BY   Name
HAVING        Groups='GROUPA' AND Groups = 'GROUPB'
0
 

Author Comment

by:intoxicated_curveball
ID: 40397380
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
 

Author Comment

by:intoxicated_curveball
ID: 40397402
No I don't think HAVING is right as it's only used for AGGREGATE functions...
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40397412
did the solution I posted not work? if not what results did you get and what were you expecting to get?
0
 

Author Comment

by:intoxicated_curveball
ID: 40397415
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40397417
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
 

Author Comment

by:intoxicated_curveball
ID: 40397418
Big Monty

Your solution would also return those in either group A and group B, but they need to be in BOTH groups.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40397422
just change the OR to AND and it should give you what you want
0
 

Author Comment

by:intoxicated_curveball
ID: 40397440
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40397441
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
 

Author Comment

by:intoxicated_curveball
ID: 40397446
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
 

Author Comment

by:intoxicated_curveball
ID: 40397491
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40397654
;with cte as (
select name. group from
<table>
where group in ('groupA', 'GroupB')
)

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

Author Closing Comment

by:intoxicated_curveball
ID: 40397664
Thank you!!!!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question