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?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?|||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|||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:

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|||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.|||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|||You might check yesterday's thread (http://www.dbforums.com/t989683.html) on this topic.

-PatP