[UPHPU] SQL help. I think I need an outer join.

[Tyler Gee]

On 9/18/06, Smith, Jeff <Jeff.Smith at hollycorp.com> wrote:
> Okay I have two tables.  One for posts and one for comments.  I want to return the whole post row and a count of all the comments.  I have it working...almost.  It returns the posts and the comment count but only for posts that have comments.  I have fairly sure I need an outer join.  I have been reading and reading and my head is about to explode.  Do you guys see what I am doing wrong?
>
> "SELECT post . * , comment.body AS body, count( * ) AS 'commentCount'
> FROM `comment` , `post`
> WHERE `post.post_ID` =`comment.post`

The problem is that this where statement (above) limits what you can
get from the 'comment' table since you are essentially on looking for
things with a  comment.post (is that an id?).

Try something like:

SELECT post . * , comment.body AS body, count( * ) AS 'commentCount'
FROM post
LEFT JOIN comment
ON post.post_id=comment.post
GROUP BY `post.post_ID`
ORDER BY `post.datePublished` DESC
LIMIT ".$mysql["feedLimit"].";"

> AND `post.feed` = '".$mysql["feedName"]."'
> GROUP BY `post.post_ID`
> ORDER BY `post.datePublished` DESC
> LIMIT ".$mysql["feedLimit"].";"
>
> Any help would be appreciated.  If my head explodes it could get messy and I don't want to do that to my roommate.
>
> Jeff Smith
>
> _______________________________________________
>
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
>


-- 
~Tyler