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
