How to Use Exists in self join-CodePudding

I want those Id whose Orgorder never equal to 1.

CREATE TABLE [dbo].[TEST](
    [ORGORDER] [int] NULL,
    [Id] [int] NOT NULL,
    [ORGTYPE] [varchar](30) NULL,
    ORGID INT NULL,
    [LEAD] [decimal](19, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE] ,ORGID, [LEAD]) VALUES (1, 100, N'ABC',1, NULL)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ABC',2, 0)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ACD',1, NULL)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 101, N'ABC',0, 0)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (2, 101, N'ABC',4, NULL)
GO

I am using exists but getting my result. Expected result is -

ID
101

CodePudding user response：

Use a subquery in a NOT EXISTS clause, linking the subquery table to the outer query table by ID:

SELECT DISTINCT T1.ID
FROM dbo.TEST AS T1
WHERE NOT EXISTS (
    SELECT *
    FROM dbo.TEST AS T2
    WHERE T1.ID = T2.ID
    AND T2.ORGORDER = 1
)

db<>fiddle

CodePudding user response：

An option would be using an aggregation with a suitable HAVING clause such as

SELECT [Id]
  FROM [dbo].[TEST]
 GROUP BY [Id]
HAVING SUM(CASE WHEN [ORGORDER] = 1 THEN 1 ELSE 0 END) = 0

where if there's at least one value equals to 1 for the concerned column([ORGORDER]), then that [Id] column won't be listed as result.

Demo

CodePudding user response：

You can do this with one pass of the data, and order all ORGORDER = 1 first, then if it's the first row and it has the ORGORDER value you want to exclude, you can just ignore it.

;WITH x AS 
(
  SELECT Id, rn = ROW_NUMBER() OVER 
    (PARTITION BY Id ORDER BY CASE WHEN ORGORDER = 1 THEN 1 ELSE 2 END)
  FROM dbo.TEST
)
SELECT Id FROM x WHERE rn = 1 AND ORGORDER <> 1;

Example db<>fiddle