Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    4

    return records that are related to other records

    hi guys!,

    I am quite familiar with datbase design and SQL, but I need your help to the problem I am facing right now!

    I have a table in the database I am building now, in which I store words, their meaning and their etymology. Some of the words are derivatives of words in other records. For example, I may have one main word looking like:
    word_id derivative_of word maening etymology
    word_id - "analyze" "meaning of the word" "etymology"
    word_id "analyze" "analyzable" "meaning of the word" (no etymology)

    Whenever I want to get all the words that contain a word in their etymology, I run the proper query and everything goes OK. What I cannot do though, is to return the derivatives of the words along with the main words, when a word in the etymology exists. The main words have etymology only; their derivatives do not, as they are assumed to have the same etymology as their parent!
    Do you think of a good design so that I get the word AND its derivtive(s) in one query?
    One thought is to run the simple query, then programmatically ask for each derivative according to the returned parent word and then merge the 2 queries.
    Another approach would be to store the derivatives in another table and return all the main words as well as the linked derivatives from the other. But this approach would not allow me to query one table when searching for parts in the word field.
    Do you have any thoughts of a better design?
    Thanks in advance,

    Manos

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Post the DDL for your tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2008
    Posts
    4
    [QUOTE=blindman]Post the DDL for your tables.[/
    The database is in MS Access. The definition of fields (if I am not mistakn this is what you are asking me for) is the following:


    WordId (autonumber)
    Derivative (memo)
    MainWord (memo)
    Explanation (memo)
    Etymology (memo)


    Is this what you asked me to post?

    Thanks again,

    Manos

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Well, the first thing that knocks me back in my seat is that you are using memo for all your datatypes.
    Not only is this overkill, and a performance crusher, but the solution to your problem is going to rely on joining the table to itself on the Derivative and MainWord columns.
    I wouldn't even store MainWord in your table. I'd store the WordID of the MainWord.... MainWordID.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2008
    Posts
    4
    Quote Originally Posted by blindman
    Well, the first thing that knocks me back in my seat is that you are using memo for all your datatypes.
    Not only is this overkill, and a performance crusher, but the solution to your problem is going to rely on joining the table to itself on the Derivative and MainWord columns.
    I wouldn't even store MainWord in your table. I'd store the WordID of the MainWord.... MainWordID.
    You are right, but then again I did not want to take any chance and trim any data fields. Are you suggesting that in the derivative field I should save its parent's wordID?
    If so, have you got any suggestion as to how to retrieve the derivatives along with the main word?

    Thanks again,

    Manos

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yes.
    A join.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2008
    Posts
    4
    Quote Originally Posted by blindman
    Yes.
    A join.
    thanks alot for your quick replies. You suggestion did work.

    For reference to other people, I write the methodology I followed.

    Derivatives have now the parent word's ID and I build a typical query which would return all the words according to the criteria given in the query.

    In order to solve the problem announced here, would be solved by inserting another table and joining it in the query build.

    Thanks again,


    Manos

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by unc_manos
    hi guys!,

    I am quite familiar with datbase design and SQL
    Respectfully....no. You are not. But you are on your way.
    Good luck, and check back with us if you need help with anything else.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •