Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: need help for building a select query

    Hi,

    I'm new in using sql language. Please help me to build a select query for following situation:

    I have 2 tables as below:

    Sales Table

    BookName MonthSales LibrarySales BookStoreSales
    x 2010.11 5 1
    x 2010.12 2 3
    y 2010.10 1 2
    y 2010.11 3 2
    y 2010.12 4 1

    Plan Table

    BookName MonthPlan LibraryPlan BookStorePlan
    x 2010.12 10 3
    x 2011.01 10 3
    x 2011.02 5 3
    y 2010.11 5 3
    y 2010.12 5 3
    y 2011.01 5 3
    y 2011.02 5 3


    I need to display the result in such way to have 1 row/BookName/Month, as below:

    BookName Month LibrarySales BookStoreSales LibraryPlan BookStorePlan
    x 2010.11 5 1 NULL NULL
    x 2010.12 2 3 10 3
    x 2011.01 NULL NULL 10 3
    x 2011.02 NULL NULL 5 3
    y 2010.10 1 2 NULL NULL
    y 2010.11 3 2 5 3
    y 2010.12 4 1 5 3
    y 2011.01 NULL NULL 5 3
    y 2011.02 NULL NULL 5 3

    How can I do that?

    Thank you,
    Adrian

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    We're not going to do your homework questions for you. Take a stab at solving the problem, post what you come up with, and we'll give you some tips.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have made a working SQL script for your problem.

    I noticed Blindman has replied before I submitted my solution. I too made a comment about homework, but the solution is not mainstream (simple INNER JOINs or an occasional LEFT OUTER JOIN) so I made an exception at first, but I'll second Blindman.

    I'll give you a hint, my solution uses a FULL OUTER JOIN.
    Try to come up with something, and we'll help you further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have a solution involving GROUP BY and a UNION derived table, if the student would like to try that direction
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd go with the full outer join.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the UNION subquery predates the full outer join, and will work even in database systems which, for whatever reason, do not (yet!) support full outer join

    speaking of which, how do you accomplish a left outer join without left outer join syntax (including the ghastly += and (*) bastardizations)? hint: EXISTS subqueries supported
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •