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. Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/ Share on other sites More sharing options...
fenway Posted March 11, 2008 Share Posted March 11, 2008 What do you get now? Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-489901 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. Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-489971 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 Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-490065 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? Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-491158 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(). Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-491237 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? Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-491373 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. Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-491469 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 Link to comment https://forums.phpfreaks.com/topic/95687-left-join-help/#findComment-493112 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.