GroupBy [message #426691] |
Mon, 19 October 2009 00:46  |
panchpan
Messages: 34 Registered: October 2007
|
Member |
|
|
Hello.
I have a table emp with fields name , id, skills. Consider one employee has 3 entries due to having 3 different skills - How can I populate only records in output with employees with 3 skills?
Thank you
|
|
|
|
|
Re: GroupBy [message #426701 is a reply to message #426695] |
Mon, 19 October 2009 01:25   |
panchpan
Messages: 34 Registered: October 2007
|
Member |
|
|
Detailed case:
Create table emp with fields name, id, skills - all are varchar.
Inserted below entries in table:
name id skills
A 1 S1
B 2 S2
A 1 S2
A 1 S3
C 3 S4
The expected output is:
name id skills
A 1 S1
A 1 S2
A 1 S3
Because, I wanted to extract employees who has 3 skills.
Hope, I could explain the question little better now.
thank you
|
|
|
|
Re: GroupBy [message #426715 is a reply to message #426701] |
Mon, 19 October 2009 02:15   |
panchpan
Messages: 34 Registered: October 2007
|
Member |
|
|
Please find the CREATE/INSERT.
CREATE TABLE emp
(name char(50),
id int(1),
skills char(2));
INSERT INTO emp
VALUES ('A', 1 , 'S1');
INSERT INTO emp
VALUES ('B', 2 , 'S2');
INSERT INTO emp
VALUES ('A', 1 , 'S2');
INSERT INTO emp
VALUES ('A', 1 , 'S3');
INSERT INTO emp
VALUES ('C', 3 , 'S4');
Expected Result:
name id skills
A 1 S1
A 1 S2
A 1 S3
|
|
|
|
Re: GroupBy [message #426724 is a reply to message #426715] |
Mon, 19 October 2009 03:02  |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
You can use GROUP BY and HAVING too:
SELECT * FROM emp
WHERE ID IN
(SELECT ID FROM emp
GROUP BY ID
HAVING COUNT(DISTINCT skills) = 3
);
If there are only DISTINCT skills (NOT NULL) in Your table, simple use COUNT(*).
|
|
|