Post reply

How do I frame this query

  • June 20, 2009 at 11:45 pm

    #135379

    Hi,

    I need help in phrasing this simple query.

    I have two tables

    1. Category table, it consists of categoryid and productid

    2. product table, it consists of categoryid, productid, productname, etc.

    Now I need to display 3 categories with 4 products in each category.

    Its not necessary that all categories will have 4 products, so first i want to find if there are 4 products in that particular category , if yes then display it.

    Further I want to input the number of categories and products from user end.

    eg. if the user asks 1 category and 2 products of the same category i should be able to display that

    or he may ask 5 categorys and all products.

    Can anyone help please

    Regards

    cmrhema

  • June 21, 2009 at 3:30 am

    #1012988

    Please see the link in my signature on how to post sample data.

    Considering your number of visits, I'd expect you to know how to ask question that get fast answers.

    You need to show some effort, too.

    [Sarcasm = ON]

    SSC isn't an abbreviation of SimpleSqlCodegenerator.

    [Sarcasm = OFF]


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 21, 2009 at 3:46 am

    #1012993

    As mentioned above; please provide some sample data.

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • June 21, 2009 at 5:06 am

    #1013007

    Ok I am providing you with sample data

    Category Id CategoryName

    1 Gifts

    2 Sports

    3 Caskets

    4 Cards

    Product Id CategoryIdProductName

    31Bag

    41Camera 3D

    51MousePad

    101LampShade

    111NamePlate

    121Box

    62Apron

    72Baby

    82Cup

    1033Casket Spray with Fabric Drape

    1043Full Cover Hanging Casket Spray

    1053Green Casket Spray with Apple

    1063Infant Casket Spray with Daisies

    1073Interior Casket Lid Arrangement

    The user will input 3,2(ie, 3 categories with 2 products each), the output should be

    31Bag

    41Camera 3D

    62Apron

    72Baby

    1033Casket Spray with Fabric Drape

    1043Full Cover Hanging Casket Spray

    If he gives 2,4(ie. 2 categories with 3 products each) the output should be as follows, it should not take the category number 2 as it has only 2 products

    31Bag

    41Camera 3D

    51MousePad

    101LampShade

    1033Casket Spray with Fabric Drape

    1043Full Cover Hanging Casket Spray

    1053Green Casket Spray with Apple

    1063Infant Casket Spray with Daisies

    I hope I have made myself clear. In case of doubts please ask me

    Regards

    cmrhema

  • June 21, 2009 at 5:22 am

    #1013011

    It seems like you've decided not to follow the article I referred to, since the way you provided sample data is named "The Wrong Way to Post Data".

    Also, please take the time to show us what you've tried so far and where you get stuck.

    Hint: I would use a CTE together with ROW_NUMBER() OVER(PARTITION BY CategoryId ORDER BY Id)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 21, 2009 at 4:11 pm

    #1013093

    cmrhema (6/21/2009)

    Ok I am providing you with sample data

    The reason folks as for sample data and direct you to the link that Lutz did is because they like to test their solutions before they post code. The reason they direct you to the link is because they're providing solutions out of the goodness of their heart and the format you provided the data in isn't readily consumable by a database.

    Take the time to follow the link that was recommended and do what it says. For that tiny bit of work on your part, you'll get a fully tested answer in the form of some great code. Without the readily consumable data, many folks will either skip your post altogether or they'll just make textual recommendations. Me? I spend a good amount of time telling people this same thing... over, and over, and over. Read the link... do what it says. You won't be disappointed.

    Also, it's important to show folks that you've tried especially on this site. 😉 You should also post the code that shows that you've at least thought about your own problem.

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

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

Viewing 6 posts - 1 through 5 (of 5 total)

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