Reputation: 43
We are attempting to modify a process in our system and I am unable to come up with a query that would retrieve the correct data. The system currently retrieves data from a terms table using the titlekey
from the author info table, meaning all authors have the same terms. We want to be able to add specific terms for an author, cut still allow other authors to use the terms associated with the title. I'm not sure what SQL
would retrieve the proper term row based on the author row.
My thought was to add an author column to the terms table. For row 2 in the author info table we would like row 2 retrieved from the term table. But for rows 1 and 3 in the author table we would like row 1 retrieved from the term table. This would be a parameterized view. Is it possible to have one query perform the retrieval? If so can you provide a `FoxPro SQL` sample?
Example:
Table of author info:
Key AuthorKey TitleKey
--- --------- --------
1 1 1
2 2 1
3 3 1
Table of terms:
Key AuthorKey TitleKey Term
--- --------- -------- ----
1 0 1 .50
2 2 0 .60
Given Author key 2 and Title key 1, row 2 in the author info table, we would like the .60 term returned from the table of terms.
I apologize if I wasn't clear so here is an example using the mock tables above.
If I set the titlekey view parameter to 1 and the authorkey viewparamter to 2 I would like .60, term key of 2, to be retrieved.
If I set the titlekey view parameter to 1 and the authorkey viewparamter to 3 I would like .50, term key of 1, to be retrieved.
Upvotes: 1
Views: 2528
Reputation: 2888
Your database seems incorrectly normalized. The duplicate keys don't help.
Assuming that your data is for some sort of payroll for a publishing system (i.e., each AUTHOR has a TITLE, and is paid at a set TERM), your table structure would be easier and simpler as follows. (Note that you'll have to edit this for it to actually run in FoxPro.)
CREATE TABLE author (
authorID I AUTOINC PRIMARY KEY,
titleID I,
termID I
)
CREATE TABLE title (
titleID I AUTOINC PRIMARY KEY,
termID I
)
CREATE TABLE term (
termID I AUTOINC PRIMARY KEY,
rate N(3,2)
)
You could then select the authors, their titles, and their effective rates using a query as follows, using a subquery to choose the effective termID
for each author.
SELECT sq.authorID, sq.titleID, R.rate
FROM (
SELECT A.authorID, T.titleID, NVL(A.termID, T.termID) AS termID
FROM author AS A
INNER JOIN title as T ON A.titleID = T.titleID
) as sq
LEFT OUTER JOIN term as R
on sq.termID = R.termID
You may also want to instead define term
as just a straight numeric value, unless there are other columns not noted here. If term is just "how many pennies per word an author is paid", it can go right on TITLE or AUTHOR as a straight N(3,2)
or even a Y
.
Upvotes: 0
Reputation: 48179
Your question is very unclear, and I THINK I have what you are looking for. Let me try to restate the post... correct me if I'm incorrect.
You have a list of authors... At a bare minimum, they have a "Title" key that all authors get a term rate regardless of specific join... However, under certain conditions, we have a term that should supersede the default "term", but only if there is a corresponding term record where the author's Key ID has a match. If there IS an author-based Key, you want the rate of THAT instance instead of the default.
That said, I am starting the query with the authors table. I am doing TWO joins to the TERMS table using different aliases, and joining on the respective Author and Title keys. The NVL() I have will look FIRST for the "By Author" alias and get it's term value. If there IS no "By Author" value, it gets the fall-back value by "By Title" alias instance which is applicable to all authors as you originally indicated.
I've grabbed extra columns so you can see the correlation of data and confirm it is as you are expecting.
select;
a.Key,;
a.AuthorKey,;
a.TitleKey,;
ByAuthor.Term as TermByAuthor,;
ByTitle.Term as TermByTitle,;
NVL( ByAuthor.Term, ByTitle.Term ) as ChosenTerm;
FROM ;
author a ;
LEFT JOIN terms as ByAuthor ;
ON a.authorKey = ByAuthor.AuthorKey;
LEFT JOIN terms as ByTitle ;
ON a.TitleKey = ByTitle.TitleKey
If you are looking for a SPECIFIC Author, just put a
WHERE a.AuthorKey = whateverValue
Upvotes: 1
Reputation: 34197
This may not be valid "FoxPro SQL" but I believe it answers your question.
SELECT
Term.Term
FROM Term
INNER JOIN Author ON Author.Key = Term.Key
AND Author.AuthorKey = @AuthorKey
AND Author.TitleKey = @TitleKey
Setting @AuthorKey = 2
and @TitleKey = 1
would produce .60
as in your example.
This assumes that Author.Key and Term.Key are each unique columns in their respective tables and that the combination of Author.AuthorKey, Author.TitleKey
is also a unique key of the Author
table.
Upvotes: 0