I tried this query and it only pulled up entries where
Twain was one of the alternate authors. It didn't retrieve
any where he was the primary author. I added another
parameter to the WHERE clause to get this:
SELECT Entries.TitleStatement, Authors_1.AuthorName AS MainAuth,
Authors.AuthorName AS AllAuth
FROM (Entries INNER JOIN (Authors INNER JOIN OA_Link
ON Authors.AuthorID = OA_Link.OA_NameID)
ON Entries.EntryID = OA_Link.OA_EntryID)
INNER JOIN Authors AS Authors_1
ON Entries.AuthorID = Authors_1.AuthorID
WHERE ((Authors.AuthorName LIKE "*twain*")
OR (Authors_1.AuthorName LIKE "*twain*"))
ORDER BY Entries.EntryID, Authors.AuthorName;
And it retrieved more records. But what it *didn't* retrieve
were records where twain was the primary author but there were
no alternate authors at all for the record. Hmmm, this is so
close. I couldn't get UNION to do what I wanted, if I could
figure out how to tweak this solution to do everything it
would be ideal.
BTW. I knew you could alias field names, like Authors.AuthorName
AS AllAuth, but I didn't know you could alias tables in your join
sections, like INNER JOIN Authors AS Authors_1. I only started
working with databases about a year ago and I only learn new SQL
as the job demands. This trick looks useful, if I could only
figure out exactly what it does.
Thanks for spending the time to really help out with this!
Arvin
Bruce Pick wrote:
Here's a better solution - lets yo specify an author, and get all books
they worked on, with the correct primary author.
SELECT Entries.EntryID, Entries.TitleStatement, Authors_1.AuthorName AS
MainAuth, Authors.AuthorName AS AllAuth
FROM (Entries INNER JOIN (Authors INNER JOIN OA_Link ON Authors.AuthorID
= OA_Link.OA_NameID) ON Entries.EntryID = OA_Link.OA_EntryID) INNER JOIN
Authors AS Authors_1 ON Entries.AuthorID = Authors_1.AuthorID
WHERE (((Authors.AuthorName)="twain"))
ORDER BY Entries.EntryID, Authors.AuthorName;
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++
To send email, remove all < > and [ ] and the junk ~ between:
[brucepick1]
< a~t >
[comcast.net]
Arvin Portlock wrote:
I can't come up with a query that works. Can anyone help?
Conceptually the relationships are easy to describe. I have
a table for books (Entries), a table for authors (Authors),
and a linking table between the two because books often
have more than one author (OA_Link). This situation is
simple and common. A query to list each title and all
the authors associated with that title looks like this:
SELECT Entries.TitleStatement, Authors.AuthorName
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_EntryID)
ON Authors.AuthorID = OA_Link.OA_NameID
WHERE Authors.AuthorName Like "*Twain*";
Unfortunately there is an added twist which I can't
resolve. There is also present the concept of the
"Main Author." The single individual who holds primary
responsibility for the book. The designer of the
database chose to create a one-to-one link directly
from Entries to Authors without going through the
linking table. With the query above, I can only retrieve
books which have "Twain" as an added author, but not
when Twain is the principle author. If I do a query
on principle authors only, it looks like this:
SELECT Entries.TitleStatement, Authors.AuthorName
FROM Authors INNER JOIN Entries
ON Authors.AuthorID = Entries.AuthorID
WHERE Authors.AuthorName Like "*Twain*";
How do I combine the two to do a query that searches
both Authors as Principle Author as well as Authors as
Added Authors? I need a pure SQL solution rather than
something that uses saved queries.
Thanks to anyone who can help me!
Arvin
