Date: 02/07/05 (MySQL Communtiy)    Keywords: mysql, database, sql, web

For a website, I have to write a suite of scripts interacting with a database of bibliographic references. Some pages will have an embedded request for a set of the latest titles by an author, and some pages will have an embedded list of titles from which to generate full references. Additionally, I have to build a simple search engine that will query by author, title, publication or abstract.

The search engine is a later problem, and I've already got searching for an embedded list of titles working. So right now I have to solve the author problem. I'm a MySQL newbie and am having problems with the following dilemma:

The problem with citations is that there's no fixed number of authors for a book. As a consequence, the database has a table for the papers, a table for the authors, and a link table with columns for a paper's foreign key and author's foreign key. A paper with three authors populates three rows of the link table.

My challenge is that to pull a complete citation when I search by author, I currently have to make two database queries:
SELECT name, id_people, papers_fk FROM papers pa, link li, people pe WHERE name LIKE 'Smith' AND pa.id_paper=li.papers_fk AND pe.id_people=li.people_fk
pulls the IDs of papers that Smith has contributed to.

I take that list of IDs and loop through
SELECT name, id_people, title, id_paper FROM papers pa, link li, people pe WHERE li.papers_fk='$titleid' AND pa.id_paper='$titleid' AND pe.id_people=li.people_fk
entering a different paper ID into $titleid on each loop to generate arrays of citations. A script will distill the arrays into comma-separated lists of authors and format the results.

My question is: Is there a way to distill this into a single query? Which is to say: Can I use a single query to get the complete list of authors for each title that Smith has contributed to?

The approach I have now will work, even if it ends up sending a lot of queries for each web page hit, but being able to streamline this query will both make this page more efficient and give me a leg up on writing the full text (citation + abstract) search section.

Source: http://www.livejournal.com/community/mysql/47657.html