Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Many-to-many relation

Posted on 1998-07-30
Medium Priority
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 160 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.   :)
Independent Software Vendors: 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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

604 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