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.   :)
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.


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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now