Database query returning from multiple tables?
Results 1 to 3 of 3

Thread: Database query returning from multiple tables?

  1. #1
    Senior Member
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    153

    Database query returning from multiple tables?

    I am trying to do the following but am not having any luck. Is there a way to do this with one query?

    Here are my tables and their fields
    Table: Articles
    Fields: article_id, title, date_posted, etc...

    Table: News
    Fields: news_id, title, date_posted, etc...

    Table: Forum
    Fields: forum_id, title, date_posted, etc...

    Table: Comments
    Fields: comment_id, section_id, category_id, comment, date_posted, etc...
    A bit of information on the fields in Comments:
    category_id - can be either 1, 2 or 3 (if it's 1 it relates to the Forum, 2 relates to the News and 3 relates to Articles)
    section_id - connects to the primary ID "forum_id", "news_id" and "article_id" depending on what the "category_id" is.

    What I want to do is return all "Forum" items (whether they have any "Comments" or not) and also all "Articles" & "News" items that HAVE comments. These would be Ordered BY date_posted in the "Comments" table (if a comment exists) OR by date_posted in the "Forum" table (since only the Forum table is one that is allowed to have no comments show up)

    Anyone know if this is possible to do with one query? Sorry if I am not that clear having a hard time to explain...

  2. #2
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,845
    I'd come up with the same sort of thing as bradgrafelman; the only difference is that I might include one more column in the results, e.g.
    Code:
                article_id id, "article" post_type, title, MAX(c.date_posted) date_posted
    to indicate the type of record (article, newsitem, or forum post) each represents.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  3. #3
    Senior Member
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    153
    Thanks guys that worked great.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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