Visual FoxPro Query

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.