Very Computer

I have two tables which have 1 to many releation. Say A is the table at the
1 side and B is the table at the many side. A(1)-----B(many)

I want to combine these into one field. I know that I can do this with UNION
query. But I want to list a record from A and immediatly after this I want
to list releated records from B.

A                       B
----                   ----
a1  ------------ b1
      ------------ b2
      ------------ b3
a2
a3
a4
a5  ------------ b4
      ------------ b5

...........

I want to have a output like this:
a1
b1
b2
b3
a2
a3
a4
a5
b4
b5

Is there any SQL solution for this?

Thanks.

Ali Sadik Kumlali

--
----------------------------------
Ali Sadyk Kumlaly

    There is no T-SQL way to do what you want. That is a hierarchical set of
data, and you'd be better served by having ADO construct it for you with the
SHAPE command.

Regards,
D. Patrick Hoerter

create table A
(
 c1 char (2) primary key
)

create table B
(
 c1 char (2)
          references A (c1),
 c2 char (2),
  primary key (c1, c2)
)
go

insert into A values ('a1')
insert into A values ('a2')
insert into A values ('a3')
insert into A values ('a4')
insert into A values ('a5')

insert into B values ('a1', 'b1')
insert into B values ('a1', 'b2')
insert into B values ('a1', 'b3')
insert into B values ('a5', 'b4')
insert into B values ('a5', 'b5')
go

create view Seq
as
select a1.c1, b1.c2,
            seqA = (select count(*) from A as a2
                           where a2.c1 <= a1.c1),
            seqB = (select count(*) from B as b2
                           where b2.c2 <= b1.c2)
from A as a1
left outer join B as b1
  on (a1.c1 = b1.c1)
go

select c1
from (select c1, seqA, seqB = 0 from Seq
              UNION
          select c2, seqA, seqB from Seq
          where seqB <> 0) as s
order by seqA, seqB

------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.