Solved

Many-to-many relation

Posted on 1998-07-30
7
176 Views
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?
0
Comment
Question by:Zoroaster
  • 4
  • 3
7 Comments
 
LVL 5

Accepted Solution

by:
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.  :)

Don
0
 

Author Comment

by:Zoroaster
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?
0
 
LVL 5

Expert Comment

by:mayhew
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.   :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Zoroaster
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.


0
 

Author Comment

by:Zoroaster
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 :-))
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1359125
Glad to be of assistance.

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

don@mayhewnet.com
0
 

Author Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 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

22 Experts available now in Live!

Get 1:1 Help Now