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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.   :)
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

695 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