Jump to content


Photo

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


  • Please log in to reply
2 replies to this topic

#1 Z24_2000

Z24_2000
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 05 September 2003 - 01:58 PM

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 :cry:

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 06 September 2003 - 07:13 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Z24_2000

Z24_2000
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 07 September 2003 - 05:20 AM

Thanks Barand :D,

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! :lol:

Thanks again,
Andrew




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users