Many-to-many relation

Posted on 1998-07-30
Last Modified: 2010-04-04
I have a db with publications and authors. And because an author can have many publications and publications can have more than one author, this has to be a m-to-m relation. So I used a third table: the link-table.

What is the best way to display the list of publications in a grid and the list of authors in another one? Publications is my master source for the link table, which is at his turn the master source for the authors table. I suppose this is not right, because I see multiple items from the link table, but only one corresponding item from the authors table.

Can anyone help me out?
Question by:Zoroaster
  • 4
  • 3

Accepted Solution

mayhew earned 40 total points
ID: 1359120
Zoroaster, I'll show you exactly what I did to get a many to many relationship to show up in grids.

First I created the tables and filled them with info as follows:
create table pub(
pubid int,
pubinfo varchar(25))

create table aut(
autid int,
autinfo varchar(25))

create table xpubaut(
pubseq int,
autseq int)

insert into pub values (1,'Life')
insert into pub values (2,'Times')
insert into pub values (3,'Clue')
insert into pub values (4,'Bible')
insert into pub values (5,'Expectations')

insert into aut values (1,'John')
insert into aut values (2,'Don')
insert into aut values (3,'Matt')
insert into aut values (4,'Joe')
insert into aut values (5,'Karen')

insert into xpubaut values (1,1)
insert into xpubaut values (1,2)
insert into xpubaut values (2,2)
insert into xpubaut values (3,3)
insert into xpubaut values (4,4)
insert into xpubaut values (5,5)
insert into xpubaut values (5,2)

xpubaut is my cross-reference table that links publications with authors.

I'm sure you set up your tables correctly.  I'm just including mine so that you can see if we did something a little differently.  :)

Then on a form in Delphi, I used two TQuery's, two TDatasources and two DBGrids (one each for the pub table and the aut table).  I pointed the queries to the database (with the database property) and I pointed the author query's datasource property to the pub datasource (basic detail-master setup).

My SQL in the pub query is:
select * from pub

My SQL in the aut query is:
select * from aut a, xpubaut b
where b.pubseq = :pubid
and a.autid = b.autseq

I hope this does what you wanted.  Give it a try and let us know.  :)


Author Comment

ID: 1359121
Yes, I get the data from both the link table and the corresponding authors data in the same grid (detail). That's already that.
But the pub table (master) and the detail table don't work synchroneously. If I go to a publication I want to see only the authors names of that particular publication. Now I see all the authors names in the detail table. Can this be solved in any way?

Expert Comment

ID: 1359122
Yes.  That's what the directions in my previous post will give you.

If this is not happening for you, your SQL string for the detail might be wrong.  Did you check it against mine?

select * from aut a, xpubaut b
where b.pubseq = :pubid
and a.autid = b.autseq

The second line "where b.pubseq = :pubid" will give you the effect you want.  It will only show the records in the detail where the id matches the id of the current row in the master.

Also, be sure that the datasource property of your detail TQuery is pointing to the TDatasource that references the master Tquery.

Let me know.   :)
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


Author Comment

ID: 1359123
I did'n use the : , because I left no space between = and : and he gave an error on it. It seems to work now, except that the detail doesn't refresh if I scroll through the master. I see only  the authors of the first publication even if I move to another publication. But that is also the fact with the grids I first used and they were working properly. So I suppose something else has gone wrong.

Anyway, you helped me a lot and I will add a few points for your patience :-). Thanks, Ron.


Author Comment

ID: 1359124
Don't worry, everything works fine now. Guess I learned a lot :-)
Thanks again, Don (and not Ron as I typed in my previous comment :-))

Expert Comment

ID: 1359125
Glad to be of assistance.

Feel free to e-mail me if you have any more questions about it.  :)

Author Comment

ID: 1359126
Thanks, Don, I will gladly accept your offer if I need help.

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to manage invalidate between two tvirtualstringtree in same form? 1 114
oracle global variables 4 68
Delphi Yen format 3 34
Dynamically Created Query 3 55
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question