Post reply

Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

  • June 16, 2014 at 8:50 pm

    #1722121

    Thanks, Jeff, for this great article.

    I have one small editorial comment.

    In the section Filtering for "2" Products, the query is the one below:

    --===== Find Customers that bought either "A" OR "B"

    -- and count the DISTINCT number of products each bought.

    -- The filter in the WHERE clause causes an error here.

    SELECT CustomerID,

    FROM #Purchase

    WHERE ProductCode IN ('A','B')

    AND COUNT(DISTINCT ProductCode) = 2

    GROUP BY CustomerID

    ;

    The error is supposed to be Msg 147, Level 15, State 1, Line 1.... But the error returned when the code above is run is:

    Msg 156, Level 15, State 1, Line 5

    Incorrect syntax near the keyword 'FROM'.

    Because, I think, of the comma after CustomerID in the SELECT clause.

    Not a huge deal, but given how precise your articles and comments are, I think it will help those following the steps.

    Thanks as always for such detailed and informative work!!

    -webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • June 17, 2014 at 2:45 pm

    #1722521

    Jeff Moden (6/13/2014)

    ColdCoffee (6/13/2014)

    Do we have a readymade 10 million/1million row generator for this problem?

    Heh... apparently, you didn't read the article where is says... 😉

    The code above isn't adequate for performance testing. For those that want to explore and compare solutions of their own, I've attached code to build a million row test table at the bottom of this article in the "Resources" link.

    I read the article when ti was first published. after that, i was following the discussion 🙂

    When i ran the code, the logical reads were less but the elapsed time was higher than mister.magoo's code. i dint do a extensive testing

Viewing 2 posts - 166 through 166 (of 166 total)

You must be logged in to reply to this topic. Login to reply