Re-write a query in SQL Server 2012

Question

I have 3 tables:

• order: contains the list of all the orders;
• product: contains the list of all products for each order;
• productVersion: contains multiple versions for each product.

I want to write a query to get all the orders that have at least one product whose only version is productType = 'holder' and is mandatory. I have pasted the query that I've written below. My solution goes through all the products for all the orders to find the result set.

For example if I have 5 orders and each of those orders have 6 products each then my query will go through 6 products for each order to determine the result set. Although this method works, this goes through a lot of rows that can be skipped. I want to write the query in such a way wherein if I find an order with a product that satisfies the criteria then I want to skip evaluating the rest of the products in that order.

Expected result:

orderId
1
3

Create Table #product
(
productId Int primary key,
orderId Int,
productName varchar(100)
)
Create Nonclustered index idx_orderId On #product(orderId)
Create Table #productVersion
(
productId Int,
version Int,
productType varchar(100),
mandatory bit
)
Create Table #orders
(
orderId Int primary key,
ordername varchar(100),
orderdate date
)
Insert Into #orders values (1,'cycles','02/22/2023'),(2,'cars','02/01/2023'),(3,'bikes','01/24/2023')
Insert Into #product values(1,1,'Abc'),(2,1,'XYZ'),(3,1,'SWT'),
 (4,2,'Camry'),(5,2,'Audi'),
 (6,3,'Hero'),(7,3,'Harley'),(8,3,'Band')
Insert Into #productVersion values(1,0,'holder',0),(1,1,'pdf',0),
 (2,0,'holder',1),(3,0,'holder',1),
 (6,0,'holder',1),(7,0,'holder',0),(7,1,'pdf',0),
 (8,0,'holder',1)
Select distinct o.orderid 
from #orders o
join #product p
on o.orderId = p.orderId
join #productVersion pv
on p.productId = pv.productId
Where productType= 'holder'
 And mandatory = 1
 And Exists(select pv_in.productId
 from #productVersion pv_in
 where pv_in.productid = pv.productid
 group by pv_in.productId
 having count(pv_in.productId) = 1
 )
/* Drop Tables */
--Drop Table If Exists #orders
--Drop Table If Exists #product
--Drop Table If Exists #productVersion

Thank you for your time!

• lifeisajourney: It's all in the block of code.

Answer 1

I would start with getting a list of the IDs of the products

whose only version is productType = 'holder' and is mandatory

This is how I would go about it:

SELECT
 productId
FROM
 #productVersion
GROUP BY
 productId
HAVING
 COUNT(*) = 1
 AND MAX(productType) = 'holder'
 AND MAX(CAST(mandatory AS int)) = 1
;

The above can then be used as a derived table and joined to #product to get the order items with such products:

SELECT
 *
FROM
 #product AS p
 INNER JOIN
 (
 SELECT
 productId
 FROM
 #productVersion
 GROUP BY
 productId
 HAVING
 COUNT(*) = 1
 AND MAX(productType) = 'holder'
 AND MAX(CAST(mandatory AS int)) = 1
 ) AS pv ON p.productId = pv.productId
;

The final step would be to use the last query in an EXISTS check when selecting from #orders:

SELECT
 o.orderId
FROM
 #orders AS o
WHERE
 EXISTS
 (
 SELECT
 *
 FROM
 #product AS p
 INNER JOIN
 (
 SELECT
 productId
 FROM
 #productVersion
 GROUP BY
 productId
 HAVING
 COUNT(*) = 1
 AND MAX(productType) = 'holder'
 AND MAX(CAST(mandatory AS int)) = 1
 ) AS pv ON p.productId = pv.productId
 WHERE
 p.orderId = o.orderId
 )
;

Or, if just the orderId is needed in the output, there is no need to involve #orders at all:

SELECT DISTINCT
 p.orderId
FROM
 #product AS p
 INNER JOIN
 (
 SELECT
 productId
 FROM
 #productVersion
 GROUP BY
 productId
 HAVING
 COUNT(*) = 1
 AND MAX(productType) = 'holder'
 AND MAX(CAST(mandatory AS int)) = 1
 ) AS pv ON p.productId = pv.productId
;

My solution can be tested and played with live at dbfiddle.uk.

• lifeisajourney: Thank you! The solution provided faces the same issue I was trying to avoid i.e. accessing all the product versions. If an order has 5 products of type holder and mandatory = 1, as soon as I find the first product version with that criteria I want to stop further processing for the other versions in that order. For eg, order 1 has products 1,2,3,4,5. Let's say product 1, 3, and 5 have product versions with type holder and mandatory=1 and nothing else. When I find product 1, I want to stop right there instead of counting the versions of 3 and 5. Does that make sense?
• Andriy M: @lifeisajourney: I think I understand. But I don't believe there's reason for you to worry. SQL is a declarative language. Your primary concern should be to tell the server what you want, in as clear a way as possible, leaving the engine to figure out how to achieve the result in a most optimal way. Sometimes your query might be too slow and you have to take measures. Usually you do so when the problem is evident, though. Until then, focus on clarity and maintainability. As it is, my alternatives might even be unnecessary if your current query hasn't caused any actual performance issues.