[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

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!
0
jasonbrandt3
Asked:
jasonbrandt3
1 Solution
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now