Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unanswered: Best query||design for table>subtable

    Hi,

    I've had few times the same problem and i'd like to go over it.
    I'm facing this kind of data:

    say that i need to store documents, all of them have a set of fields in common and all also have specific fields:

    articles [ TITLE, AUTHOR, DATE, PIC, EXTRACT, KEYWORDS, ... ]
    news [ TITLE, AUTHOR, DATE, URL, SOURCE, KEYWORDS, ... ]
    ...

    my first solution was to store all of this data like this:
    doc [ TITLE, AUTHOR, DATE, KEYWORDS, TABLENAME, TABLEID ]
    doc_articles [ ID, PIC, EXTRACT ]
    doc_news [ID, URL, SOURCE ]

    It works fine BUT i need 2 queries for each doc to get its corresponding specific data.

    Is there a way to dynamicaly link the corresponding fields leading to a result set with all table fields??

    Is there another way to design tables to make it easier?

    Thx in advance for reading,

    ++

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check to see how your database engine supports the JOIN operation. I think it will do what you want.

    -PatP

  3. #3
    Join Date
    Feb 2006
    Posts
    2
    Yes, i may not have been precise enough,

    I actually JOIN when i know TABLENAME:

    If i want news rows i do a

    select * from doc,doc_news left join doc.TABLEID=doc_news.ID where doc.TABLENAME=doc_news


    BUT i sometimes get a doc row without knowing if its a news or an article.... i then need a 2nd query to get the specific table rows.

    Would there be a query to select the doc row AND the specific table row with one query?

    I'd say, Is there dynamic SQL possilities like

    SELECT * from doc,doc.TABLENAME where doc.ID=X ??
    (doesn't work "as is" in mysql)...

    ++

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use LEFT OUTER JOINs to join the doc table to both the news and the articles table

    each PK in doc will have only one matching FK in either news or articles, so bob's your uncle -- still only one query!!



    but check your syntax carefully, because this isn't even close to being valid syntax --

    select * from doc
    ,doc_news
    left join doc.TABLEID=doc_news.ID
    where doc.TABLENAME=doc_news
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you must continue with your current design you could also consider aliasing
    eg
    select Article.docID as ArtDoc, Article.Author as ArtAuthor from blah

    not nice but it may help
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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