Results 1 to 5 of 5
  1. #1

    SQL Count from 2 tables?

    Greetings,

    Learning SQL a bit here and have a question. I have two tables. One stores articles and the other stores comments on those articles. The basic structure is:

    Articles
    ---------
    id
    text

    Comments
    -------------
    id
    comment
    user_id
    article_id

    Now, the articles.id and comments.article_id relate. Is there a way I can select all the articles table AND count the number of comments based on that particular article? I can't seem to figure out how to do it but it seems simple enough.

    Any help would be GREATLY appreciated!

  2. #2
    Join Date
    Sep 2002
    Location
    Illinois
    Posts
    2,304
    You can do it using group by

    Code:
    select
       a.text ArticleName,
       count(*) NumberOfComments
    from Articles a
       inner join Comments c ON c.article_id = a.ID
    group by a.text
    This will give you number of comments per article.

    null
    Last edited by null; 10-31-2005 at 09:39 PM.
    How's my programming? Call 1-800-DEV-NULL

  3. #3
    Actually, it should be simpler than that, if I understand you correctly:

    Code:
    select count(article_id) from Articles left join Comments on ID = article_id where ID = 1;
    Hope it helps.

    Brandon

  4. #4
    Join Date
    Jan 2003
    Posts
    1,715
    I don't think you understand it correctly, and it's not a very good query, anyway. You don't use anything from Articles, but still join to it. If you just want the comment count for a given article ID, it's
    Code:
    SELECT count(*) FROM Comments WHERE article_id = 1;
    If you want a comment count for each article, null's query is correct.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  5. #5
    Oh nevermind, I was confused; but yes the query null gave should work.

    I just got a bit confused is all

Posting Permissions

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