Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    484
    Thanks
    10
    Thanked 33 Times in 32 Posts

    Correct syntax for getting information from two tables

    I know this is possible and I've done it quite often in the past. But I haven't used MySQL in months(been doing some C++ work. Not that it has anything to do with C++.) Anyhow, what I have is two table Posts and Comments and I'm trying to get all the posts and then count the number of comments those posts have. I do not need to get any information from the comments table other than counting how many comments there are to a single post. I want to do this with mysql only.
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,801
    Thanks
    160
    Thanked 2,216 Times in 2,203 Posts
    Blog Entries
    1
    Please post the relevant column names of your tables.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    484
    Thanks
    10
    Thanked 33 Times in 32 Posts
    Sorry, I am just now getting on. I have figured out how to do it and return the result that I like. The only issue I have is that it always returns information even when there is no data to be returned. Btw, I am using codeigniter framework at the moment. Here is what I used:
    Code:
    $this->db->select("posts.*, COUNT(comments.id) AS numberOfComments", FALSE); 
    $this->db->where('posts.type_id',$type);
    $this->db->from('posts');
    $this->db->join('comments', 'comments.post_id = posts.id', 'left');
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^


  •  

    Posting Permissions

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