bfsog Posted March 11, 2008 Share Posted March 11, 2008 Hello. I have 2 tables. content_posts and content_comments What I am trying to do is extract all the columns from content_posts and the number of comments column from content_comments. For instance, I have a content_post row with 3 comments, so all columns from content_posts and one column from content_comments should be returned. My current query is: SELECT content_posts.entry_id, content_posts.entry_date, content_posts.entry_title, content_posts.entry_text, content_posts.entry_user_date, content_posts.entry_description FROM content_posts LEFT JOIN content_comments ON content_posts.entry_id = content_comments.entry_id GROUP BY content_posts.entry_id however this isnt really working, it does not return anything from content_comments My schemas are below content_posts entry_id [int, auto inc, primary] entry_date [timestamp] entry_title [varchar] entry_text [text] content_comments comment_id [int, auto inc, primary] entry_id [int, acts as foreign key] blah blah blah.. [the rest is not important? Any help is appreciated. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 11, 2008 Share Posted March 11, 2008 What do you get now? Quote Link to comment Share on other sites More sharing options...
bfsog Posted March 11, 2008 Author Share Posted March 11, 2008 I just get the columns from the first table. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 12, 2008 Share Posted March 12, 2008 You are selecting columns from the first table only Quote Link to comment Share on other sites More sharing options...
bfsog Posted March 13, 2008 Author Share Posted March 13, 2008 I dont need anything from the second table though, just how many comments it has for each content_posts row So, my query needs to 1: Select all rows from content_posts 2: For each row from content_posts get the number of comments it has from content_comments Right now I am using 1 queries to solve this, any ideaS? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 13, 2008 Share Posted March 13, 2008 1. It does that. 2. Just add a count(). Quote Link to comment Share on other sites More sharing options...
bfsog Posted March 13, 2008 Author Share Posted March 13, 2008 I have added a count and no matter what I do 1 is returned for a row which has no comments, I really am at my wits end. Can anyone help? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 13, 2008 Share Posted March 13, 2008 Let's see some sample data, table structure, and all the queries involved. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2008 Share Posted March 16, 2008 SELECT p.entry_id, p.entry_date, p.entry_title, p.entry_text, p.entry_user_date, p.entry_description, COUNT(c.comment_id) as comments FROM content_posts p LEFT JOIN content_comments c ON p.entry_id = c.entry_id GROUP BY p.entry_id Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.