Board index » delphi » Help : Multiple record display of records for M:N relationship

Help : Multiple record display of records for M:N relationship

Can anyone please help?

I have two Table A and B with M:N relationship. Thus, I've created
another table AB to hold the key field of Table A and B repectively.

I wish to display all the record related in Table B on Grid B when a
record is selected in Grid A.

I've link the tables as such
Table A ---  Table AB ---  Table B

However, when I select one record in Grid A. Only one record will be
displayed on Grid B which is due to only one record in Table AB is
selected correspondingly. Thus, Grid B only display the detail of record
which is selected in Table AB.

Is there a way to display all the related records?

Thanks in advance for any reply.

Low Han Ming
lo...@singnet.com.sg

 

Re:Help : Multiple record display of records for M:N relationship


Quote
lowhm wrote:

> Can anyone please help?

> I have two Table A and B with M:N relationship. Thus, I've created
> another table AB to hold the key field of Table A and B repectively.

> I wish to display all the record related in Table B on Grid B when a
> record is selected in Grid A.

> I've link the tables as such
> Table A ---  Table AB ---  Table B

> However, when I select one record in Grid A. Only one record will be
> displayed on Grid B which is due to only one record in Table AB is
> selected correspondingly. Thus, Grid B only display the detail of record
> which is selected in Table AB.

> Is there a way to display all the related records?

Use a TQuery...
Sylvain.

Re:Help : Multiple record display of records for M:N relationship


Why don't you abandon your method of relating B with AB and try this:

link B to AB manually! using

TableB.SETRANGE([TableAB.fieldByName(YourKeyField).value],[TableAB.fieldByNa
me(YourKeyField).value]);

(ensure TableB.indexname points to an index starting with YourKeyField)
just put this in TableAB's DataSource's OnDataChange event.

HTH,

--
Paul Motyer
pa...@linuxserver.pccity.com.au
SoftStuff, Croydon, Vic,  Australia, 3136.

lowhm <lo...@singnet.com.sg> wrote in article
<3266334E.2...@singnet.com.sg>...

Quote
> Can anyone please help?

> I have two Table A and B with M:N relationship. Thus, I've created
> another table AB to hold the key field of Table A and B repectively.

> I wish to display all the record related in Table B on Grid B when a
> record is selected in Grid A.

> I've link the tables as such
> Table A ---  Table AB ---  Table B

> However, when I select one record in Grid A. Only one record will be
> displayed on Grid B which is due to only one record in Table AB is
> selected correspondingly. Thus, Grid B only display the detail of record
> which is selected in Table AB.

> Is there a way to display all the related records?

> Thanks in advance for any reply.

> Low Han Ming
> lo...@singnet.com.sg

Re:Help : Multiple record display of records for M:N relationship


Use a TQuery component for the second grid and set its datasource property to
that used by the first grid.
The SQL would look like this
select B.*
from B, AB
where
 AB.AKey = :AKey
 and B.BKey = AB.BKey
order by {whatever}

Note that the Parameter for the query must be named the same as the key field
in Table A, also since you're doing a join the query cannot be live.

Hope this helps.

--
*****************************************
* Sean C. Fisher, sfis...@cadvision.com
* SCF Consulting, Calgary, AB
*
* 'There are more objects in Mainframe
*  and the Net than are formulated in
*  your paradigm, Bob'
*****************************************

Re:Help : Multiple record display of records for M:N relationship


Quote
"Paul Motyer" <pa...@linusserver.pccity.com.au> wrote:
>Why don't you abandon your method of relating B with AB and try this:

>link B to AB manually! using

>TableB.SETRANGE([TableAB.fieldByName(YourKeyField).value],[TableAB.fieldByNa
>me(YourKeyField).value]);

>(ensure TableB.indexname points to an index starting with YourKeyField)
>just put this in TableAB's DataSource's OnDataChange event.

The obvious response, use a TQuery, isn't always satisfactory.
TQueries are much slower that TTables.  I have manually filled string
grids after parsing through 3 and more tables to solve this exact
problem.  Pauls solution is elegant... but surely won't work, it'll
repeat exactly the same 'error' that the original poster complains of,
ie it'll only show one record,  the record in B matching the record in
AB where the cursor settles after the last OnDataChange.

Rob
--------------------------------
email: RobDa...@wheel.demon.co
       Rob.Da...@unn.ac.uk
Web:   http://www.unn.ac.uk/~isu8

Other Threads