SQL Retrieve ALL if match a condition

Cenk · February 6, 2023, 3:09pm

Hi,

One last question for today I want to get all of the BulkPurchaseRequests only if all of the BulkPurchase Status = 1. There are 2 different tables.

Thank you.

USE [GameAPI]
GO
/****** Object:  Table [dbo].[BulkPurchaseRequest]    Script Date: 6.02.2023 18:06:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BulkPurchaseRequest](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TotalAmount] [int] NOT NULL,
	[ProductCode] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Email] [nvarchar](150) NULL,
	[Status] [int] NULL,
	[RequestDateTime] [datetime] NULL,
 CONSTRAINT [PK_dbo.BulkPurchaseRequest] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BulkPurchases]    Script Date: 6.02.2023 18:06:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BulkPurchases](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BulkPurchaseRequestId] [int] NOT NULL,
	[Amount] [int] NOT NULL,
	[ProductCode] [nvarchar](50) NOT NULL,
	[Status] [int] NULL,
	[PurchaseDateTime] [datetime] NULL,
 CONSTRAINT [PK_dbo.BulkPurchases] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[BulkPurchaseRequest] ON 
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (1, 20000, N'0187021', N'0187021', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-04T21:53:43.677' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (2, 1, N'0186481', N'Test', N'System.Security.Claims.ClaimsPrincipal', 1, CAST(N'2023-01-27T16:32:29.823' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (3, 3, N'0186449', N'Test', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-27T17:16:21.127' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (4, 20000, N'OYNPLS000005', N'Test', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-28T14:58:10.787' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (5, 1000, N'018000007166', N'Test', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-28T14:59:56.380' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (6, 1, N'OYNPLS000001', N'Test', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-28T17:29:43.190' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (7, 1, N'018000007166', N'deneme', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-29T17:57:53.593' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (8, 2, N'0187617', N'test test test', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-29T17:58:46.460' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (9, 5, N'OYNPLS000037', N'MDM', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-30T11:13:01.920' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (10, 22, N'10714', N'TEST TEST TEST', N'kizildagcenk@gmail.com', 1, CAST(N'2023-01-30T11:14:18.697' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (11, 1000, N'OYNPLS000035', N'Test CENK', N'kizildagcenk@gmail.com', 1, CAST(N'2023-02-02T16:47:01.807' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (12, 1050, N'10710', N'TEST Cenk1', N'kizildagcenk@gmail.com', 1, CAST(N'2023-02-02T16:49:56.083' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (13, 3000, N'OYNPLS000043', N't', N'kizildagcenk@gmail.com', 1, CAST(N'2023-02-02T16:52:28.583' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (14, 1000, N'018000007166', N'd', N'kizildagcenk@gmail.com', 1, CAST(N'2023-02-02T16:56:41.810' AS DateTime))
GO
INSERT [dbo].[BulkPurchaseRequest] ([Id], [TotalAmount], [ProductCode], [Description], [Email], [Status], [RequestDateTime]) VALUES (15, 175, N'0186481', N'fff', N'kizildagcenk@gmail.com', 1, CAST(N'2023-02-02T18:00:54.207' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[BulkPurchaseRequest] OFF
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] ON 
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (8, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.487' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (9, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.533' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (10, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.537' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (11, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.543' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (12, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.547' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (13, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.553' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (14, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.560' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (15, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.567' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (16, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.570' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (17, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.577' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (18, 15, 100, N'0187209', 1, CAST(N'2023-02-02T18:00:54.533' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkPurchaseRequestId], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (19, 15, 75, N'0187209', 1, CAST(N'2023-02-02T18:00:54.570' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] OFF
GO

Cenk · February 6, 2023, 3:17pm

Tried something like this but no records were retrieved. There has to be 2 actually.

SELECT *
FROM BulkPurchaseRequest BPR
INNER JOIN BulkPurchases BP ON BPR.Id = BP.BulkPurchaseRequestId
WHERE BPR.Id = ALL (
SELECT BulkPurchaseRequestId
FROM BulkPurchases
GROUP BY BulkPurchaseRequestId
HAVING MIN(Status)=1
)

harishgg1 · February 6, 2023, 3:24pm

Hi

Hope this helps

Select
ID
From
BulkPurchaseRequests
Where
BulkPurchase Status = 1
And
ID Not In
(

Select
ID
From
BulkPurchaseRequests
Where
BulkPurchase Status = 0
)

harishgg1 · February 6, 2023, 3:28pm

Another way

Hope this helps

Select
ID ,
Sum ( Case when BulkPurchase Status = 1 then 0 else 1
end )
From
BulkPurchaseRequests
Having
Sum ( Case when BulkPurchase Status = 1 then
0 else 1 end ) = 0

Cenk · February 6, 2023, 4:05pm

No Joins?

Msg 4145, Level 15, State 1, Line 6
An expression of non-boolean type specified in a context where a condition is expected, near 'Status'.
Msg 4145, Level 15, State 1, Line 16
An expression of non-boolean type specified in a context where a condition is expected, near 'Status'.

Cenk · February 6, 2023, 4:56pm

This works. But I bet you experts have a much better solution

SELECT BPR.Id
FROM BulkPurchaseRequest BPR
INNER JOIN BulkPurchases BP ON BPR.Id = BP.BulkPurchaseRequestId
WHERE BPR.Id IN (
SELECT BulkPurchaseRequestId
FROM BulkPurchases
GROUP BY BulkPurchaseRequestId
HAVING MIN(Status)=1
)
GROUP BY BPR.Id

robert_volk · February 6, 2023, 5:00pm

Using IN is fine, this works for me:

SELECT *
FROM BulkPurchaseRequest BPR
INNER JOIN BulkPurchases BP ON BPR.Id = BP.BulkPurchaseRequestId
WHERE BPR.Id IN(
SELECT BulkPurchaseRequestId
FROM BulkPurchases
GROUP BY BulkPurchaseRequestId
HAVING MIN(Status)=1)

The only difference from your original was IN rather that = ALL().

Cenk · February 6, 2023, 5:03pm

robert_volk:

SELECT *
FROM BulkPurchaseRequest BPR
INNER JOIN BulkPurchases BP ON BPR.Id = BP.BulkPurchaseRequestId
WHERE BPR.Id IN(
SELECT BulkPurchaseRequestId
FROM BulkPurchases
GROUP BY BulkPurchaseRequestId
HAVING MIN(Status)=1)

I used group by because data are multiplied.

robert_volk · February 6, 2023, 5:05pm

No worries, whatever gives you the correct results.

harishgg1 · February 6, 2023, 5:47pm

hi

my solutions are correct .. just syntax issues .. i was not at my desktop

any how .. now i am at my desktop

Select 
         ID 
From 
       BulkPurchaseRequest 
Where 
         Status = 1 
             And 
        ID Not In (Select ID From BulkPurchaseRequest Where Status = 0)

harishgg1 · February 6, 2023, 5:49pm

hi my other solution

Select 
     ID , Sum ( Case when Status = 1 then 0 else 1 end )
From
    BulkPurchaseRequest
group by 
    ID 
Having
     Sum ( Case when  Status = 1 then 0 else 1 end ) = 0

harishgg1 · February 6, 2023, 6:28pm

hi

a third way of doing this

all depends on your requirements

performance
data is very less
network round trips
etc etc

select ID From BulkPurchaseRequest Where Status = 1
EXCEPT
select ID From BulkPurchaseRequest Where Status = 0

Cenk · February 7, 2023, 4:58am

Thank you.