asked on

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!

ste5an

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

Lauden

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’)

Pooja Katiyar Verma

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

Vikas Garg

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

ste5an

@all, please guys, use the code tag or the embed code snippet button from the toolbar...

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jasonbrandt3

ASKER

This was the most efficient code of the several listed.