Solved

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

Posted on 2014-10-22
19
160 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 32

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 39

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
 
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 32

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 32

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 39

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now