Refactoring subquery to JOIN and CROSS APPLY, get only row for each record in parent table

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP












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.










share|improve this question



















  • 3





    Help me write this query in SQL.

    – Erik Darling
    Jan 21 at 15:46











  • 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















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.










share|improve this question



















  • 3





    Help me write this query in SQL.

    – Erik Darling
    Jan 21 at 15:46











  • 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













2












2








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.










share|improve this question
















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.







sql-server sql-server-2008-r2 sql-server-2014 join cross-apply






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 21 at 19:21







Marc.2377

















asked Jan 21 at 15:05









Marc.2377Marc.2377

1376




1376







  • 3





    Help me write this query in SQL.

    – Erik Darling
    Jan 21 at 15:46











  • 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












  • 3





    Help me write this query in SQL.

    – Erik Darling
    Jan 21 at 15:46











  • 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







3




3





Help me write this query in SQL.

– Erik Darling
Jan 21 at 15:46





Help me write this query in SQL.

– Erik Darling
Jan 21 at 15:46













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





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





@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










1 Answer
1






active

oldest

votes


















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





share|improve this answer
























    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227687%2frefactoring-subquery-to-join-and-cross-apply-get-only-row-for-each-record-in-pa%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    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





    share|improve this answer





























      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





      share|improve this answer



























        4












        4








        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





        share|improve this answer















        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






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Jan 21 at 19:48









        Marc.2377

        1376




        1376










        answered Jan 21 at 18:09









        Martin SmithMartin Smith

        63k10169253




        63k10169253



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227687%2frefactoring-subquery-to-join-and-cross-apply-get-only-row-for-each-record-in-pa%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown






            Popular posts from this blog

            How to check contact read email or not when send email to Individual?

            Displaying single band from multi-band raster using QGIS

            How many registers does an x86_64 CPU actually have?