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!

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.