Question on Recursive

  • I have three tables in my database as follow:

    select BrandName, ProductName, URL from dbo.ProductsTb

    select ID,KeyName from dbo.GoogleKey

    select ID,KeyWords from dbo.GoogleWords

    these three tables have N rows, now my problem is I want to display this data in resultset using select query, please follow my excel sheet I have specified an eg.

    please pardon me for any mistakes!!

  • You might want to start with CROSS JOIN on those three tables.

    Unfortunately, there is no rule that would explain why there is no entry for

    BrandNameTwo ProductNameOne aviator eyewear

    or any other valid combination that is presented for BrandNameOne.

    If you'd like to see a coded version please provide ready to use sample data as described in the first link in my signature.



    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]

  • Hello,

    below are the tables with sample data which you can use it!!

    CREATE TABLE [dbo].[googlewords](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [WordsName] [varchar](100) NULL

    )

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<'eyewear'>)

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<'frame'>)

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<'frames'>)

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<'eyeglasses'>)

    CREATE TABLE [dbo].[googlekey](

    [ID] [int]IDENTITY(1,1) NOT NULL,

    [KeyName] [varchar](100) NULL

    )

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<' '>)

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<'aviator'>)

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<'oversize'>)

    INSERT INTO [dbo].[googlewords]

    ([WordsName])

    VALUES

    (<'rectangular'>)

    CREATE TABLE [dbo].[ProductsTb](

    [ProductID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

    [ProductName] [varchar](255) NULL,

    [brandname] [varchar](100) NULL,

    )

    INSERT INTO [dbo].[productstb]

    [BrandName], [Productname]

    VALUES

    (<'Alain Mikli'>, <'3456 G'>)

    INSERT INTO [dbo].[productstb]

    [BrandName], [Productname]

    VALUES

    (<'Burberry'>, <'5456'>)

    INSERT INTO [dbo].[productstb]

    [BrandName], [Productname]

    VALUES

    (<'Costa Del Mar'>, <'AAA'>)

    INSERT INTO [dbo].[productstb]

    [BrandName], [Productname]

    VALUES

    (<'Diesel'>, <'ENFORCER'>)

  • Here's the Cross Join syntax together with the string concatenation.

    As a side note: I had to modify your sample data script to actually work. It's good practice to test such scipts in an empty database to make sure it will produce the expected result...

    SELECT [productstb].[BrandName]+' '+[productstb].[Productname]+' '+[googlekey].[KeyName]+' '+[googlewords].[WordsName]

    FROM [dbo].[googlewords]

    CROSS JOIN [dbo].[googlekey]

    CROSS JOIN [dbo].[productstb]



    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]

  • Hello,

    This works thank you very much... A ++ SUPPORT !!

  • biren (11/17/2010)


    Hello,

    This works thank you very much... A ++ SUPPORT !!

    Support? We get paid for this? *scratches head* Hey, Steeeeeeeve..... where's my check dood?!


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • no I am just thanking for the help lol

  • Craig Farrell (11/17/2010)


    biren (11/17/2010)


    Hello,

    This works thank you very much... A ++ SUPPORT !!

    Support? We get paid for this? *scratches head* Hey, Steeeeeeeve..... where's my check dood?!

    Depending on the mood you're in the support statement can be put into perspective regarding not getting paid in a slightly different way:

    " I didn't have to pay for the support, so it didn't have any value either" 😀

    @biren: Explicit NOT directed at you!! I guess you figured how much it helps to provide ready to use sample data to get coded and tested answers... 😉



    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]

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

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