SQL query assistance?

I have a table containing a user id_num and a group_id.  The combination of the two fields are the primary key.  For example a user may have multiple rows:

id_num       GroupId
1070                CD
1070                adv
1070                 alm

So I'm having trouble figuring out my query.  I want to return only users with the CD group_id that have no other groupids assigned.  So I want to find all CD's who do not have any other groups assigned.  Hope that makes sense!
jasonbrandt3Asked:
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.

ste5anSenior DeveloperCommented:
E.g.
DECLARE @Sample TABLE ( id_num INT, GroupId VARCHAR(255) );

INSERT INTO @Sample
VALUES	( 1070, 'CD' ),
	( 1070, 'adv' ),
	( 1070, 'alm' ),
	( 1071, 'CD' ),
	( 1072, 'alm' );

SELECT	S.id_num
FROM	@Sample S
WHERE	S.GroupId = 'CD'
EXCEPT
SELECT	S.id_num
FROM	@Sample S
WHERE	S.GroupId != 'CD';

Open in new window

0
LaudenCommented:
SELECT     YourTable.Id_num
FROM         YourTable INNER JOIN
                          (SELECT     Id_num
                            FROM          YourTable AS YourTable_1
                            GROUP BY Id_num
                            HAVING      (COUNT(GroupId) = 1)) AS tOneRecord ON YourTable.Id_num = tOneRecord.Id_num
WHERE     (YourTable.GroupId = N’CD’)
0
Pooja Katiyar VermaCommented:
Select b.id_num from mytable a, mytable b
where a.id_num =b.id_num and  a.GroupId = 'CD'
Group by b.id_num having count(b.id_num) =1
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Try this Query will give you the Results
SELECT TBL.id_num FROM
(
SELECT id_num,COUNT(GroupId) CT FROM Comb
GROUP BY id_num
) TBL
WHERE TBL.id_num IN (SELECT id_num FROM Comb WHERE GroupId = 'CD')
AND TBL.CT = 1
0
ste5anSenior DeveloperCommented:
@all, please guys, use the code tag or the embed code snippet button from the toolbar... EE message toolbar
0
PortletPaulfreelancerCommented:
This will return only users with the CD group_id that have no other groupids assigned, and it requires only a single pass of the table (others require 2)
SELECT
      id_num
FROM YourTable
GROUP BY
      id_num
HAVING COUNT(DISTINCT GroupId) = 1
AND MAX(GroupId) = 'CD'
;

Open in new window

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
jasonbrandt3Author Commented:
This was the most efficient code of the several listed.
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 2008

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.