multiple tables one result set...best solution?Temp tables?
Posted 05 September 2003 - 01:58 PM
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,
table 2 contains the following linkage information:
recid, fk_infoid, fkstaffid, fk_roleid
table 3 contains the following role (author etc...) information:
table 4 contains the following researcher information:
table 5 contains the following journal information:
table 6 contains the following publication type (book, paper etc...) information:
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!
Posted 06 September 2003 - 07:13 PM
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
Donations gratefully received
|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
Posted 07 September 2003 - 05:20 AM
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!
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users