Need Correct Query Code Please? Thx. merry Christmas SQL Kids

  • CoolKid40

    SSC Enthusiast

    Points: 186

    Compose a SQL query to retrieve all products with its most recent two orders? Products that haven’t been ordered should be listed  also. Use ERD below. Whats Correct Query Code?

     

  • drew.allen

    SSC Guru

    Points: 76739

    You'll learn a lot more if you do your own homework.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Mike01

    SSChampion

    Points: 11300

    it would be nice to at least see what you've tried so far.

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Jonathan AC Roberts

    SSCoach

    Points: 17342

    SELECT p.ID PRODUCT_ID, 
    p.PRODUCT_NAME,
    o.ID ORD_ID,
    o.DATE_ORDERED
    FROM dbo.S_PRODUCT p
    OUTER APPLY(SELECT TOP(2)
    o.ID
    o.DATE_ORDERED
    FROM dbo.S_ITEM i
    INNER JOIN dbo.S_ORD o
    ON o.ID = i.ORD_ID
    WHERE i.PRODUCT_ID = p.ID
    ORDER BY o.DATE_ORDERED DESC) o
  • Thom A

    SSC Guru

    Points: 98786

    Jonathan AC Roberts wrote:

    SELECT p.ID PRODUCT_ID, 
    p.PRODUCT_NAME,
    o.ID ORD_ID,
    o.DATE_ORDERED
    FROM dbo.S_PRODUCT p
    OUTER APPLY(SELECT TOP(2)
    o.ID
    o.DATE_ORDERED
    FROM dbo.S_ITEM i
    INNER JOIN dbo.S_ORD o
    ON o.ID = i.ORD_ID
    WHERE i.PRODUCT_ID = p.ID
    ORDER BY o.DATE_ORDERED DESC) o

    Although I believe in Christmas spirit, I don't think giving them the answer is really going to help them in the long run. :/

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jonathan AC Roberts

    SSCoach

    Points: 17342

    Thom A wrote:

    Although I believe in Christmas spirit, I don't think giving them the answer is really going to help them in the long run. :/

    I don't think you can come up with that answer very easily if you are a beginner. It's sometimes best to see an actual answer, then see out how it works, than just being stuck on a problem getting nowhere.

  • Jeff Moden

    SSC Guru

    Points: 997343

    Jonathan AC Roberts wrote:

    Thom A wrote:

    Although I believe in Christmas spirit, I don't think giving them the answer is really going to help them in the long run. :/

    I don't think you can come up with that answer very easily if you are a beginner. It's sometimes best to see an actual answer, then seeing out how it works, than just being stuck on a problem getting nowhere.

    Nice formatting, as well, Jonathan!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CoolKid40

    SSC Enthusiast

    Points: 186

    Thanks for feedback responses everyone and yes its better to work backwards from answer and possibly explain your code syntax logic. I just learning sql. Did not know what sql was until 2019.

  • Jeff Moden

    SSC Guru

    Points: 997343

    CoolKid40 wrote:

    Thanks for feedback responses everyone and yes its better to work backwards from answer and possibly explain your code syntax logic. I just learning sql. Did not know what sql was until 2019.

    With that thought in mind, have you figured out what the Outer Apply in the code is doing for you and why Jonathan didn't use CROSS APPLY, instead?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • webrunner

    SSC-Dedicated

    Points: 30403

    CoolKid40 wrote:

    Thanks for feedback responses everyone and yes its better to work backwards from answer and possibly explain your code syntax logic. I just learning sql. Did not know what sql was until 2019.

    See also these diagrams, which you can use to find your own answers or to better understand any answers others give  you.

    https://stevestedman.com/2015/05/tsql-join-types-poster-version-4-1/

     

    -- 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

Viewing 10 posts - 1 through 10 (of 10 total)

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