Linking table SQL help needed

[Arvin Portlock]

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.TitleSt atement, Authors.AuthorN ame
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_Entr yID)
ON Authors.AuthorI D = OA_Link.OA_Name ID
WHERE Authors.AuthorN ame 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.TitleSt atement, Authors.AuthorN ame
FROM Authors INNER JOIN Entries
ON Authors.AuthorI D = Entries.AuthorI D
WHERE Authors.AuthorN ame 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

[Arvin Portlock]

Ahh, I see what I need here is a UNION. In fact taking
the two queries listed below and sticking "UNION" between
them does the trick. Sorry for my ignorance but I've
never used a union before.

I guess I have a second question. My application allows
the user to search on multiple fields, Author, Title,
Subject, Call Number, all in the context of some complicated
joins. How do I incorporate a UNION into something like
that. I can see how to do it now as a standalone author
search, but not how to incorporate it into a larger SQL
statement.
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.TitleSt atement, Authors.AuthorN ame
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_Entr yID)
ON Authors.AuthorI D = OA_Link.OA_Name ID
WHERE Authors.AuthorN ame 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.TitleSt atement, Authors.AuthorN ame
FROM Authors INNER JOIN Entries
ON Authors.AuthorI D = Entries.AuthorI D
WHERE Authors.AuthorN ame 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

[Patrick W]

Have you tried a UNION query? This basically tacks the results of one
statement onto the other.

(SELECT Entries.TitleSt atement, Authors.AuthorN ame
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_Entr yID)
ON Authors.AuthorI D = OA_Link.OA_Name ID
WHERE Authors.AuthorN ame Like "*Twain*")
UNION
(SELECT Entries.TitleSt atement, Authors.AuthorN ame
FROM Authors INNER JOIN Entries
ON Authors.AuthorI D = Entries.AuthorI D
WHERE Authors.AuthorN ame Like "*Twain*");

Pat.
Arvin Portlock <ap********@hot mail.com> wrote in message news:<bv******* ****@agate.berk eley.edu>...

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.TitleSt atement, Authors.AuthorN ame
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_Entr yID)
ON Authors.AuthorI D = OA_Link.OA_Name ID
WHERE Authors.AuthorN ame 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.TitleSt atement, Authors.AuthorN ame
FROM Authors INNER JOIN Entries
ON Authors.AuthorI D = Entries.AuthorI D
WHERE Authors.AuthorN ame 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

[Bruce Pick]

Try this - I built the three tables you described and it seems to do the
job.

SELECT Entries.EntryID , Entries.TitleSt atement, Authors.AuthorN ame AS
Pr_Auth, Authors_1.Autho rName AS All_Auths
FROM ((Authors INNER JOIN Entries ON Authors.AuthorI D =
Entries.AuthorI D) INNER JOIN OA_Link ON Entries.EntryID =
OA_Link.OA_Entr yID) INNER JOIN Authors AS Authors_1 ON OA_Link.OA_Name ID
= Authors_1.Autho rID
ORDER BY Entries.EntryID , Authors.AuthorN ame;

Bruce Pick
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++
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.TitleSt atement, Authors.AuthorN ame
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_Entr yID)
ON Authors.AuthorI D = OA_Link.OA_Name ID
WHERE Authors.AuthorN ame 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.TitleSt atement, Authors.AuthorN ame
FROM Authors INNER JOIN Entries
ON Authors.AuthorI D = Entries.AuthorI D
WHERE Authors.AuthorN ame 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

[Bruce Pick]

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.TitleSt atement, Authors_1.Autho rName AS
MainAuth, Authors.AuthorN ame AS AllAuth
FROM (Entries INNER JOIN (Authors INNER JOIN OA_Link ON Authors.AuthorI D
= OA_Link.OA_Name ID) ON Entries.EntryID = OA_Link.OA_Entr yID) INNER JOIN
Authors AS Authors_1 ON Entries.AuthorI D = Authors_1.Autho rID
WHERE (((Authors.Auth orName)="twain" ))
ORDER BY Entries.EntryID , Authors.AuthorN ame;

+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++
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.TitleSt atement, Authors.AuthorN ame
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_Entr yID)
ON Authors.AuthorI D = OA_Link.OA_Name ID
WHERE Authors.AuthorN ame 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.TitleSt atement, Authors.AuthorN ame
FROM Authors INNER JOIN Entries
ON Authors.AuthorI D = Entries.AuthorI D
WHERE Authors.AuthorN ame 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

[Arvin Portlock]

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.TitleSt atement, Authors_1.Autho rName AS MainAuth,
Authors.AuthorN ame AS AllAuth
FROM (Entries INNER JOIN (Authors INNER JOIN OA_Link
ON Authors.AuthorI D = OA_Link.OA_Name ID)
ON Entries.EntryID = OA_Link.OA_Entr yID)
INNER JOIN Authors AS Authors_1
ON Entries.AuthorI D = Authors_1.Autho rID

WHERE ((Authors.Autho rName LIKE "*twain*")
OR (Authors_1.Auth orName LIKE "*twain*"))

ORDER BY Entries.EntryID , Authors.AuthorN ame;

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.AuthorN ame
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.TitleSt atement, Authors_1.Autho rName AS
MainAuth, Authors.AuthorN ame AS AllAuth
FROM (Entries INNER JOIN (Authors INNER JOIN OA_Link ON Authors.AuthorI D
= OA_Link.OA_Name ID) ON Entries.EntryID = OA_Link.OA_Entr yID) INNER JOIN
Authors AS Authors_1 ON Entries.AuthorI D = Authors_1.Autho rID
WHERE (((Authors.Auth orName)="twain" ))
ORDER BY Entries.EntryID , Authors.AuthorN ame;

+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++
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.TitleSt atement, Authors.AuthorN ame
FROM Authors
INNER JOIN (Entries INNER JOIN OA_Link
ON Entries.EntryID = OA_Link.OA_Entr yID)
ON Authors.AuthorI D = OA_Link.OA_Name ID
WHERE Authors.AuthorN ame 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.TitleSt atement, Authors.AuthorN ame
FROM Authors INNER JOIN Entries
ON Authors.AuthorI D = Entries.AuthorI D
WHERE Authors.AuthorN ame 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