CODEWITHSUNDEEP
sqlsql-servert-sql
kspearrin
kspearrin

Reputation: 10748

How do I write a query to find where an entity is the only association of a certain type?

Example:

CREATE TABLE [UserClass]
(
    [UserId]  [varchar](50) NOT NULL,
    [ClassId] [varchar](50) NOT NULL,
    [Status]  [tinyint]     NOT NULL
)

INSERT INTO [UserClass] 
VALUES ('UserA', 'ClassX', 5), ('UserA', 'ClassY', 5), ('UserA', 'ClassZ', 6),
       ('UserB', 'ClassX', 5), ('UserB', 'ClassY', 6), ('UserB', 'ClassZ', 5),
       ('UserC', 'ClassY', 6), ('UserC', 'ClassZ', 6)

I want a query to count the number of classes where a given user is the only member of a given status.

I have created the following query, which seems to work for this purpose:

DECLARE @UserId VARCHAR(50) = 'UserA'
DECLARE @Status TINYINT = 5

SELECT
    COUNT(1)
FROM
    [UserClass] UC
WHERE
    UC.[UserId] = @UserId
    AND UC.[Status] = @Status
    AND (SELECT COUNT(1)
         FROM [UserClass] UC2
         WHERE UC2.[ClassId] = UC.[ClassId]
           AND UC2.[Status] = @Status) = 1

This query uses a sub-select in the where clause.

Is there a better way to write this query in order to avoid the sub-select?

Fiddle: http://sqlfiddle.com/#!6/5c871/1

Upvotes: 1

Views: 65

Answers (3)

gotqn
gotqn

Reputation: 43636

You can do this:

WITH DS AS
(
    SELECT *
          ,COUNT(*) OVER (PARTITION BY [ClassId], [Status]) AS [Count]
    FROM [dbo].[UserClass] 
)
SELECT *
FROM DS
WHERE [Count] = 1
    AND [UserID] = @UserId
    AND @Status = @Status;

enter image description here

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I do not think that there is a way to do it without subselect, however, I would use the NOT EXISTS for it:

SELECT UserClass.*
FROM UserClass
WHERE UserClass.[Status] = @Status and
      UserClass.UserId = @UserId and
      NOT EXISTS 
      (
          SELECT 1
          FROM [UserClass] UC
          WHERE UC.[Status] = UserClass.[Status] and 
                UC.[ClassId] = UserClass.[ClassId] and 
                UC.[UserId] != UserClass.[UserId]  
      )

Upvotes: 0

Hogan
Hogan

Reputation: 70523

Here is the typical way you would do this:

SELECT ClassID 
FROM UserClass
GROUP BY ClassID
HAVING COUNT(*) = 1

Upvotes: -1

Related Questions