intoxicated_curveball
asked on
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.
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.
I assume that the names are in one or two columns and the groups are in another?
Or am I missing something?
select * from myTable where groupName in ('groupa', 'groupb')Or am I missing something?
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')
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')
SELECT Name
FROM MyTable
GROUP BY Name
HAVING (Groups IN ('GROUPA', 'GROUPB'))
FROM MyTable
GROUP BY Name
HAVING (Groups IN ('GROUPA', 'GROUPB'))
ASKER
Sorry the only stipulation is I need it to return the DISTINCT names having BOTH groups
If I use
Seems to returns even if person is only in groupA.
If I use
select * from myTable where groupName in ('groupa', 'groupb')Seems to returns even if person is only in groupA.
Actually...
SELECT Name
FROM MyTable
GROUP BY Name
HAVING Groups='GROUPA' AND Groups = 'GROUPB'
SELECT Name
FROM MyTable
GROUP BY Name
HAVING Groups='GROUPA' AND Groups = 'GROUPB'
ASKER
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?
ASKER
No I don't think HAVING is right as it's only used for AGGREGATE functions...
did the solution I posted not work? if not what results did you get and what were you expecting to get?
ASKER
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.
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.
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');SELECT name
FROM Contacts
WHERE groups ='one' AND groups = 'two'
GROUP BY nameASKER
Big Monty
Your solution would also return those in either group A and group B, but they need to be in BOTH groups.
Your solution would also return those in either group A and group B, but they need to be in BOTH groups.
just change the OR to AND and it should give you what you want
ASKER
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.
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)
Can you give a sample layout of the table(s)
ASKER
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.
ASKER
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:
And the problem is I have more then two groups that actually need to be selected so it ends up being something more like
etc.
SELECT NAME FROM TABLE WHERE NAME IN
(SELECT NAME FROM TABLE WHERE GROUP = GROUPA)
WHERE GROUP = GROUPBAnd 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 = GROUPCetc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!!!!!
select distinct( nameColumn) from tbl where groupColumnName = 'GROUPA' or groupColumnName = 'GROUPB'