Combining releated fields into one field...

Combining releated fields into one field...

Post by Ali Sadik Kumlal » Thu, 15 Mar 2001 09:46:59



Hi Everybody,

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

 
 
 

Combining releated fields into one field...

Post by D. Patrick Hoerte » Thu, 15 Mar 2001 10:00:41


Ali,

    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



> Hi Everybody,

> 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



 
 
 

Combining releated fields into one field...

Post by BP Margoli » Thu, 15 Mar 2001 10:49:53


Ali,

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.



> Hi Everybody,

> 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


 
 
 

1. Combining 3 fields to be one searchable field

Hello Group,

FMP5 with Win 98

I have three fields which together make up the identifier for each record.
What I want to do is get another field to combine the selections of these 3
field to be one searchable field.

Field 1 Identifier (Text) - Drop down list to select a Letter  eg:  S
Field 2 Set (Text) - User enters a number for next set eg: 5
Field 3 Item (Number auto index) 1, 2, 3 etc.

If I use merge fields to show what I want to see it would look like this for
3 records.

S5.1
S5.2
S5.3

I need to create a field to hold this information that I can search on.

Thank you.

Toni

2. Sybase to AS/400 gateway product

3. Combining Several Fields into one field

4. DB2/400 - SQLServer

5. Combine TEXT Fields as one

6. HELP!!!! Field Sizing Problems w/ 3d genscnx driver

7. Combining multiple records into one coma-separated field

8. Indices

9. query help needed for combining fields for with one-to-many relationship

10. combining fields when one is null

11. How to combine a Date and (text field) Time into one

12. combining two record fields into one combo box

13. Some SQL,Combining 2 fields as one, Best way