multiple tables one result set...best solution?Temp tables?

[Z24_2000]

Okay,

 

I\'d hate to be long winded, but here we go...I\'m at a crucial point in my program as the foundation I use for this select stmt will be used throughout my application, which is why I came here, because I want to get it right.

 

Background: I have tables named publicationinfo(1), publicationrec(2), luroles(3), luresearher(4), lujournal(5), lupublicationtype(6).

 

table 1 contains the following publication information:

infoid, title, journalvolume, journaldate, datesubmitted,dateaccepted,

fk_typeid, fk_journalid

 

table 2 contains the following linkage information:

recid, fk_infoid, fkstaffid, fk_roleid

 

table 3 contains the following role (author etc...) information:

roleid, roledescription

 

table 4 contains the following researcher information:

staffid, name

 

table 5 contains the following journal information:

journalid, journalname

 

table 6 contains the following publication type (book, paper etc...) information:

typeid, typedescription

 

What I am trying to achieve:

I would like to output a single line per record within the publicationinfo table. Within in this line of output, I would like to see title, journalname, journalvolume, journaldate, datesubmitted, dateaccepted, typedescritption, researcher (all that took part in the publication) and role (each role would be displayed in brackets beside the researcher name).

 

I know this is pretty specific, and I don\'t want anyone to think I\'m looking for a quick answer, because I understand it\'s a pretty complicated query! I\'ve tried to figure it out on my own, and I get as far as joining pubinfo to lupubtype and lujournal, to get a single red for each publication, but I can\'t seem to link through pubrec (linkage table) to select names and roles without outputting one publication per researcher (duplicate info)...anywho, I can post the create table stmts if anyone would like them...but maybe you smart fellows can figure this one out without them, let me know! I realllllllyyyyy appreciate it!

 

Andrew

[Barand]

I\'m afraid its a fact of life when joining tables that if table A has 3 matching records in table B then you get 3 rows returned, so if a publication has several researchrs, you get several rows from the query.

 

This doesn\'t preclude you from displaying the data in a single row of a table though. You can always code it to display

 

| publication | researcher 1 | researcher 2 | researcher 3 | etc

 

 

hth

[Z24_2000]

Thanks Barand ,

 

I actually just got it working yesterday, and I coded it to display pretty much how you suggested. Thanks for the reply, I\'m sure you guys will see some more silly questions from me in the future!

 

Thanks again,

Andrew