Thread: Design problem

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    1
    Rep Power
    0

    Design problem


    Hi!
    I have one, sort of theoretical, question.

    Is it possible to create database i.e. like this:

    3 tables: A, B, C

    table A table B table C
    -------------- ------------- -------------
    id value id value id value
    -------------- ------------- -------------
    5 val1 5 val2 5 val3
    5 val4 5 val5

    and to extract data somehow with one sql query
    to get result like this:

    5 val1 val2 val3 val4 val5

    in one record?

    Thanks for any ideas
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    21
    Rep Power
    0
    You would have to do a cursor to add them all together...

    declare @id int,
    @test varchar(1000),
    @sql varchar(4000)

    set @id = 5

    declare test cursor
    for select (value) from tblA where id = @id

    open test
    fetch next from test into @test

    while @@fetch_status = 0
    begin

    set @sql = @sql + @test + ' '

    fetch next from test into @test
    end

    close test
    deallocate test



    declare test cursor
    for select (value) from tblB where id = @id

    open test
    fetch next from test into @test

    while @@fetch_status = 0
    begin

    set @sql = @sql + @test + ' '

    fetch next from test into @test
    end

    close test
    deallocate test


    declare test cursor
    for select (value) from tblC where id = @id

    open test
    fetch next from test into @test

    while @@fetch_status = 0
    begin

    set @sql = @sql + @test + ' '

    fetch next from test into @test
    end

    close test
    deallocate test


    select @id + ' ' + @sql

IMN logo majestic logo threadwatch logo seochat tools logo