By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,543 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,543 IT Pros & Developers. It's quick & easy.

Linking table SQL help needed

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.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


Nov 12 '05 #2

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

(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*")
UNION
(SELECT Entries.TitleStatement, Authors.AuthorName
FROM Authors INNER JOIN Entries
ON Authors.AuthorID = Entries.AuthorID
WHERE Authors.AuthorName Like "*Twain*");

Pat.
Arvin Portlock <ap********@hotmail.com> wrote in message news:<bv***********@agate.berkeley.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.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

Nov 12 '05 #3

P: n/a
Try this - I built the three tables you described and it seems to do the
job.

SELECT Entries.EntryID, Entries.TitleStatement, Authors.AuthorName AS
Pr_Auth, Authors_1.AuthorName AS All_Auths
FROM ((Authors INNER JOIN Entries ON Authors.AuthorID =
Entries.AuthorID) INNER JOIN OA_Link ON Entries.EntryID =
OA_Link.OA_EntryID) INNER JOIN Authors AS Authors_1 ON OA_Link.OA_NameID
= Authors_1.AuthorID
ORDER BY Entries.EntryID, Authors.AuthorName;

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.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

Nov 12 '05 #4

P: n/a
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

Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.