2

Given the following query:

SELECT
  p.ProductName,
  CASE
    WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
    THEN
      1
    ELSE
      0
    END
  AS HasImage,
  (SELECT Sum(StockBalance) FROM Product WHERE ProductSuperID = p.ProductSuperID) AS StockBalance,
  CASE
    WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND Price IS NULL)
    AND EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND DiscountPrice IS NULL)
    THEN
      0
    ELSE
      1
    END
    AS HasPrice
FROM ProductSuper p

-- SCHEMA
CREATE TABLE ProductSuper
(
  ProductSuperID int,
  ProductName varchar(255)
)
CREATE TABLE Product
(
  ProdID int,
  ProductSuperID int,
  HasImage bit,
  StockBalance int,
  Price decimal(10,2),
  DiscountPrice decimal(10,2)
)
INSERT INTO ProductSuper
  (ProductSuperID, ProductName)
VALUES
  (1, 'Product 1'),
  (2, 'Product 2')
INSERT INTO Product
  (ProductSuperID, HasImage, StockBalance, Price, DiscountPrice)
VALUES
  (1, 0, 10, 10.00, 9.00),
  (1, 0, 0, 10.00, 9.00),
  (2, 0, 10, 10.00, 9.00),
  (2, 0, 2, 10.00, 9.00),
  (2, 1, 5, 10.00, 9.00)


I want to learn how could I best rewrite it to use either JOIN or CROSS APPLY, if anything to avoid some code duplication. I tried writing a JOIN-based version (and one with APPLY) but I'm getting more than one result for each row in the ProductSuper table, whereas I only want one row.

i.e. expected result:

+-------------+-----+-----+-----+
|  Product 1  |  0  |  10 |  1  |
+-------------+-----+-----+-----+
|  Product 2  |  1  |  17 |  1  |
+-------------+-----+-----+-----+

(I'm aware that for this particular piece of code there is little benefit from rewriting, since the subqueries are fast. But still, this is only an example.)

Thanks.

  • Is there more than one record per ProdId in the Product table? That's the only way that this code makes sense, or that a simple change to a JOIN would return multiple records. However, I'd like confirmation. – Laughing Vergil Jan 21 at 16:51
  • @LaughingVergil, yes. There's only one ProdID in the ProductSuper table, which has a one-to-many relationship with the Product table. – Marc.2377 Jan 21 at 17:51
4

I'd probably structure this query as below

WITH ProductDetails
     AS (SELECT ProductSuperID,
                HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
                StockBalance = Sum(StockBalance),
                HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
         FROM   Product
         GROUP  BY ProductSuperID)
SELECT p.ProductName,
       HasImage = ISNULL(pd.HasImage,0),
       pd.StockBalance,
       HasPrice = ISNULL(pd.HasPrice,0)
FROM   ProductSuper p
       LEFT JOIN ProductDetails pd
         ON p.ProductSuperID= pd.ProductSuperID; 

As CROSS APPLY it could be written like this

SELECT ps.ProductName,
       pd.HasImage,
       pd.StockBalance,
       pd.HasPrice
FROM   ProductSuper ps
       CROSS APPLY (SELECT HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END),
                           StockBalance = Sum(StockBalance),
                           HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END
                    FROM   Product p
                    WHERE  p.ProductSuperID= ps.ProductSuperID) pd 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.