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]

Re: Linking table SQL help needed (A UNION!)

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:
[color=blue]
> 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
>[/color]

[Patrick W]

Re: Linking table SQL help needed

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 <apollock11@hot mail.com> wrote in message news:<bvrm2h$2r lc$1@agate.berk eley.edu>...[color=blue]
> 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[/color]

[Bruce Pick]

Re: Linking table SQL help needed

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:[color=blue]
> 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
>[/color]

[Bruce Pick]

Re: Linking table SQL help needed

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:[color=blue]
> 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
>[/color]

[Arvin Portlock]

Re: Linking table SQL help needed

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:
[color=blue]
> 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:
>[color=green]
> > 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
> >[/color][/color]