I created a database to store the back issues of a magazine for online access. Initially, I had two tables: "authors" and "articles". Each article had an "author_id" field which associated it with a specific entry in "authors.
I've got a bit of a quandary here. I had to restructure the database to allow for multiple authors of a single article - a rare, but important phenomenon. To do this, I've created a new table, called "AuthorLinks", with an "author_id" and an "article_id" linking authors to various articles.
Rather than rework the entire database around this concept, I figured I would just keep "author_id" as a field in "articles" so that each article has a primary author, and then any extra authors are associated through the authorLinks table. This way, most of the time we won't even need links in the authorLinks table, since most articles have only one author.
If I want to select all the articles a given author has written (say author number 26), I thought I could just generate a SQL query as such:
// Get only the title of these articles
SELECT a.title FROM ( articles a, authorLinks l) WHERE
// If the author is the primary author listed by the article, great
(a.author_id = 26
// Otherwise, we still want the article if it was linked to in the
// authorLinks table
OR (a.article_id = l.article_id AND l.author_id = 26))
Looks good. The problem with this query is the use of the "OR" statement. Since OR allows for the article link statement to be false, EVERY item in table "articleLinks" will pass the statement. Therefore, the query will bring back each item that satisfies "a.author_id=26" over and over again - for as many items there are in table "articleLinks".
So - my question is, is there any way to write a query that will do what I intended - bring back a list of articles associated with the author, but have no duplicate items? I don't want to do two separate queries, because I want the power of the SQL "ORDER BY" option for searches.