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












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
    12 hours ago











  • 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
    11 hours ago











  • @LaughingVergil, yes. There's only one ProdID in the ProductSuper table, which has a one-to-many relationship with the Product table.

    – Marc.2377
    10 hours ago
















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
    12 hours ago











  • 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
    11 hours ago











  • @LaughingVergil, yes. There's only one ProdID in the ProductSuper table, which has a one-to-many relationship with the Product table.

    – Marc.2377
    10 hours ago














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 9 hours ago







Marc.2377

















asked 13 hours ago









Marc.2377Marc.2377

1376




1376








  • 3





    Help me write this query in SQL.

    – Erik Darling
    12 hours ago











  • 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
    11 hours ago











  • @LaughingVergil, yes. There's only one ProdID in the ProductSuper table, which has a one-to-many relationship with the Product table.

    – Marc.2377
    10 hours ago














  • 3





    Help me write this query in SQL.

    – Erik Darling
    12 hours ago











  • 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
    11 hours ago











  • @LaughingVergil, yes. There's only one ProdID in the ProductSuper table, which has a one-to-many relationship with the Product table.

    – Marc.2377
    10 hours ago








3




3





Help me write this query in SQL.

– Erik Darling
12 hours ago





Help me write this query in SQL.

– Erik Darling
12 hours ago













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
11 hours ago





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
11 hours ago













@LaughingVergil, yes. There's only one ProdID in the ProductSuper table, which has a one-to-many relationship with the Product table.

– Marc.2377
10 hours ago





@LaughingVergil, yes. There's only one ProdID in the ProductSuper table, which has a one-to-many relationship with the Product table.

– Marc.2377
10 hours ago










1 Answer
1






active

oldest

votes


















3














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









    3














    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






























      3














      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




























        3












        3








        3







        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 8 hours ago









        Marc.2377

        1376




        1376










        answered 10 hours ago









        Martin SmithMartin Smith

        62.3k10168250




        62.3k10168250






























            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 make a Squid Proxy server?

            Is this a new Fibonacci Identity?

            19世紀