Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: deceptively simple join / select question

    Ok, I have two tables with a child/parent or one -> many relationship:

    parent_table:
    pid int primary key
    pname varchar

    child_table:
    cid int primary key
    pid int
    cname varchar

    Say the contents of these two tables are:

    parent_table:
    pid pname:
    1 Ben
    2 Jesse
    3 Michael

    child_table
    pid cid cname
    1 1 ben_Child1
    1 2 ben_Child2
    1 3 ben_Child3
    2 4 jesse_Child1
    2 5 jesse_Child2
    2 6 jesse_Child3
    3 7 michael_Child1
    3 8 michael_Child2
    3 9 michael_Child3

    Now what I would like to be able to do is:

    select pname, cname
    from
    parent table a,
    child_table b
    where a.pid = b.pid

    Except! Instead of getting the results in the form of:

    Ben ben_Child1
    Ben ben_Child2
    Ben ben_Child3
    ...

    I would like them in

    Ben ben_Child1 ben_Child2

    Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?

  2. #2
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    Re: deceptively simple join / select question

    Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?

    Originally posted by blm14_cu
    Ok, I have two tables with a child/parent or one -> many relationship:

    parent_table:
    pid int primary key
    pname varchar

    child_table:
    cid int primary key
    pid int
    cname varchar

    Say the contents of these two tables are:

    parent_table:
    pid pname:
    1 Ben
    2 Jesse
    3 Michael

    child_table
    pid cid cname
    1 1 ben_Child1
    1 2 ben_Child2
    1 3 ben_Child3
    2 4 jesse_Child1
    2 5 jesse_Child2
    2 6 jesse_Child3
    3 7 michael_Child1
    3 8 michael_Child2
    3 9 michael_Child3

    Now what I would like to be able to do is:

    select pname, cname
    from
    parent table a,
    child_table b
    where a.pid = b.pid

    Except! Instead of getting the results in the form of:

    Ben ben_Child1
    Ben ben_Child2
    Ben ben_Child3
    ...

    I would like them in

    Ben ben_Child1 ben_Child2

    Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?

  3. #3
    Join Date
    Jan 2004
    Posts
    32

    Re: deceptively simple join / select question

    Originally posted by JODonnell
    Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?
    No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:

    pname1 cname1
    pname1 cname2

    I want:

    pname1 cname1 cname2

  4. #4
    Join Date
    Feb 2004
    Location
    Pittsburgh, PA
    Posts
    41

    Re: deceptively simple join / select question

    Originally posted by blm14_cu
    No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:

    pname1 cname1
    pname1 cname2

    I want:

    pname1 cname1 cname2
    What about GROUP BY:
    Code:
    Select p.*,c.* From Ptable P Join Ctable C ON P.pid = C.pid Where [P.pid = C.pid] GROUP BY P.pid
    Sorry for the mess but it's almost 5.

    John

  5. #5
    Join Date
    Jan 2004
    Posts
    32
    Still no good. The group by wont help because I'm not doing any sums or avgs or counts or anything. Adding the group by wont change the results at all actually, from what I know.

  6. #6
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    I was bored.

    I think this is what you're looking for

    Rgds,
    Jim.


    declare @d_id int;
    declare @c_name varchar(100);
    declare @c_arr varchar(2000);
    declare @tmp varchar(100);

    declare @x table([id] int, [name] varchar(2000))

    DECLARE d cursor for
    select depid
    from dept;

    OPEN d
    FETCH NEXT FROM d INTO @d_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @tmp='';
    set @c_arr='';

    DECLARE c CURSOR FOR
    SELECT name
    FROM emp
    where deptid = @d_id

    OPEN c
    FETCH next from c into @c_name
    while @@fetch_status = 0
    BEGIN
    print @d_id
    print @c_name

    set @tmp = @c_arr
    set @c_arr = @c_name+','+@tmp
    fetch next from c into @c_name
    END
    CLOSE c
    DEALLOCATE c
    if (len(@c_arr)>1)
    begin Insert @x values(@d_id, substring(@c_arr,1,len(@c_arr)-1))end

    FETCH NEXT FROM d INTO @d_id
    END
    CLOSE d
    DEALLOCATE d

    select id, name as name from @x
    GO

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You might check yesterday's thread on this topic.

    -PatP

Posting Permissions

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