Board index » delphi » How to join 2 Tables joined together

How to join 2 Tables joined together

Hi,

I have two tables, a master table and a detail table. (One to many
relationship). What I want to be able to do, is some sort of query that will
join the two tables together and give me a grid showing all the information.

However, their's a trick:

The master table has the primary field 'Contract_Number', and numerous other
fields.

The detail table has 3 fields, 'Contract_Number','Attribute' which are both
primary, and 'Value' which is a string field.

I would like the resulting answer table to show all 'attribute' values as a
field.

ie:

If the detail table has the following records:

Contract    Attribute    Value
1                Name        Bill
1                Phone        555-2321
2                Name        Bob
3                Name        Harry
3                City            Sydney

I would like the resulting table to have

Contract Number, <some other fields in the master table in
here>,Name,Phone,City

as the result fields, and underneath the values filled in.

I know this is possible using the decision cube, but ONLY if the attribute
field was a numeric field. It's not, it's a string field.

Does anyone know of a way to do this?

Thanks & Regards

Adam Hair.

 

Re:How to join 2 Tables joined together


If it's possible to redesign your database at this stage, I would recommend
turning your "attributes" into fields in your detail table.

Detail table:

IntNo Contract    Name    Phone        City
1        1                Bill          555-2321  NULL
2        2                Bob        NULL        NULL
3        3                Harry      NULL        Sydney
4        3                Bill          NULL        Sydney

Having a "strange" database structure forces you to create "strange" and
overly complicated queries, IMOHO. However, you do lose some flexibility by
using field names instead of "attribute" values (i.e. you will have a finite
number of fields). However, with the field IntNo as a prmary key, you can
have as many records as you want that point to the same contract in the
master table.

--

Alain Quesnel
c...@compuserve.com

Quote
"Adam H." <ahair@_nospam_netconnect.com.au> wrote in message

news:3c683d9d_1@dnews...
Quote
> Hi,

> I have two tables, a master table and a detail table. (One to many
> relationship). What I want to be able to do, is some sort of query that
will
> join the two tables together and give me a grid showing all the
information.

> However, their's a trick:

> The master table has the primary field 'Contract_Number', and numerous
other
> fields.

> The detail table has 3 fields, 'Contract_Number','Attribute' which are
both
> primary, and 'Value' which is a string field.

> I would like the resulting answer table to show all 'attribute' values as
a
> field.

> ie:

> If the detail table has the following records:

> Contract    Attribute    Value
> 1                Name        Bill
> 1                Phone        555-2321
> 2                Name        Bob
> 3                Name        Harry
> 3                City            Sydney

> I would like the resulting table to have

> Contract Number, <some other fields in the master table in
> here>,Name,Phone,City

> as the result fields, and underneath the values filled in.

> I know this is possible using the decision cube, but ONLY if the attribute
> field was a numeric field. It's not, it's a string field.

> Does anyone know of a way to do this?

> Thanks & Regards

> Adam Hair.

Re:How to join 2 Tables joined together


Hi Alain,

This is not an option. In doing this, I could have simply inserted those
fields into the master table. (As it would then be a one-one relationship).
The whole purpose of having the detail table, is that I can have an infinite
number of fields.

I'm sort of chasing a true cross-tab option that does strings as well as
numeric values - but I don't know what SQL would do this for me.

Thanks & Regards

Adam Hair.

Quote
"Alain Quesnel" <cinqsanss...@compuserve.com> wrote in message

news:3c686471$1_2@dnews...
Quote
> If it's possible to redesign your database at this stage, I would
recommend
> turning your "attributes" into fields in your detail table.

> Detail table:

> IntNo Contract    Name    Phone        City
> 1        1                Bill          555-2321  NULL
> 2        2                Bob        NULL        NULL
> 3        3                Harry      NULL        Sydney
> 4        3                Bill          NULL        Sydney

> Having a "strange" database structure forces you to create "strange" and
> overly complicated queries, IMOHO. However, you do lose some flexibility
by
> using field names instead of "attribute" values (i.e. you will have a
finite
> number of fields). However, with the field IntNo as a prmary key, you can
> have as many records as you want that point to the same contract in the
> master table.

> --

> Alain Quesnel
> c...@compuserve.com

> "Adam H." <ahair@_nospam_netconnect.com.au> wrote in message
> news:3c683d9d_1@dnews...
> > Hi,

> > I have two tables, a master table and a detail table. (One to many
> > relationship). What I want to be able to do, is some sort of query that
> will
> > join the two tables together and give me a grid showing all the
> information.

> > However, their's a trick:

> > The master table has the primary field 'Contract_Number', and numerous
> other
> > fields.

> > The detail table has 3 fields, 'Contract_Number','Attribute' which are
> both
> > primary, and 'Value' which is a string field.

> > I would like the resulting answer table to show all 'attribute' values
as
> a
> > field.

> > ie:

> > If the detail table has the following records:

> > Contract    Attribute    Value
> > 1                Name        Bill
> > 1                Phone        555-2321
> > 2                Name        Bob
> > 3                Name        Harry
> > 3                City            Sydney

> > I would like the resulting table to have

> > Contract Number, <some other fields in the master table in
> > here>,Name,Phone,City

> > as the result fields, and underneath the values filled in.

> > I know this is possible using the decision cube, but ONLY if the
attribute
> > field was a numeric field. It's not, it's a string field.

> > Does anyone know of a way to do this?

> > Thanks & Regards

> > Adam Hair.

Re:How to join 2 Tables joined together


Quote
>The master table has the primary field 'Contract_Number', and numerous other
>fields.
>The detail table has 3 fields, 'Contract_Number','Attribute' which are both
>primary, and 'Value' which is a string field.
>I would like the resulting answer table to show all 'attribute' values as a
>field.

Is this Paradox?  if so the best way to do it is to create a table with the
structure you need then use a series if Insert and Update Queries to fill it.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads