#flipcode

#flipcode
Welcome to the chatterbox... 
#flipcode forumspace!

Browsing General Programming

Big fat SQL statements


 




 
Dirtysouthafrican (2)Big fat SQL statements - 2005-11-02 08:01:02
Avatar of user Dirtysouthafrican
Registered: unknown
Posts: 312

Hey all.

Lets say I have a table called posts, a table called topics, a table called users, and a table called posts_text. I want to do the following:

In a single SQL statement, obtain the last N topics, sorted by topic.updated, the number of posts to that topic (so a count involving the table posts), and who last posted (so somehow connect posts.user_id to users.user_id)

This seems simple but when I try left joins and the like things get grouped incorrectly.

Fire Elemental (18)Big fat SQL statements - 2005-11-03 08:51:36
Avatar of user Fire Elemental
Registered: unknown
Posts: 21
SELECT TOP 10
    topics.id,
    topics.subject,
    (SELECT COUNT(*) FROM Posts WHERE Posts.TopicId = topics.id) AS PostCount,
    users.username,
    topics.updated
FROM topics
INNER JOIN
    (SELECT topics.id AS TopicId, MAX(posts.id) as PostId
    FROM topics
    INNER JOIN Posts) AS LastPost ON topics.id = LastPost.TopicId
INNER JOIN Posts ON Posts.id = LastPost.PostId
INNER JOIN Users ON User.id = Posts.PosterId
GROUP BY topics.id, topics.subject
ORDER BY topics.updated DESC
Dirtysouthafrican (2)Big fat SQL statements - 2005-11-03 11:38:35
Avatar of user Dirtysouthafrican
Registered: unknown
Posts: 312

Teh googles! They do noothing! Heheh, thanks Fire. I\'ll try this out when I get home. I thought that I needed two nested selects but I thought as I didn\'t really know what I was doing that I should probably get help.

I\'m using MySQL so instead of TOP 10 I would put LIMIT 10 on the very bottom?

Dirtysouthafrican (2)Big fat SQL statements - 2005-11-03 11:39:06
Avatar of user Dirtysouthafrican
Registered: unknown
Posts: 312

*Note to self* Add stripslashes to preview code... :)

Dirtysouthafrican (2)Big fat SQL statements - 2005-11-04 08:57:35
Avatar of user Dirtysouthafrican
Registered: unknown
Posts: 312

Ok, here\'s the problem. I think the MAX(forum_posts.post_id) is not returning the latest post from within that topic, but the latest post from all topics, which isn\'t really what I need. The topic count works fine, though. Damn these SQLs =)

SELECT
forum_topics.topic_id,
forum_topics.topic_subject,
(SELECT COUNT(*) FROM forum_posts WHERE forum_posts.topic_id = forum_topics.topic_id) AS PostCount,
mw_user.user_name, mw_user.user_id, forum_posts.post_id,
forum_topics.topic_updated
FROM forum_topics
INNER JOIN
(SELECT forum_topics.topic_id AS TopicId, MAX(forum_posts.post_id) as PostId
FROM forum_topics
INNER JOIN forum_posts GROUP BY TopicId) AS LastPost ON forum_topics.topic_id = LastPost.TopicId
INNER JOIN forum_posts ON forum_posts.post_id = LastPost.PostId
INNER JOIN mw_user ON mw_user.user_id = forum_posts.poster_id
WHERE forum_topics.forum_id = 4
GROUP BY forum_topics.topic_id, forum_topics.topic_subject
ORDER BY forum_topics.topic_updated DESC LIMIT 10
Fire Elemental (18)Big fat SQL statements - 2005-11-04 10:33:48
Avatar of user Fire Elemental
Registered: unknown
Posts: 21
SELECT forum_topics.topic_id AS TopicId, MAX(forum_posts.post_id) as PostId
FROM forum_topics
INNER JOIN forum_posts GROUP BY TopicId

I\'m not sure how that would even parse :o

the \"INNER JOIN forum_posts\" should be

\"INNER JOIN forum_posts ON forum_posts.topicId = forum_topics.id\"


 
You need to log in in order to reply to this topic.
generated:August 28, 2008, 12:03 am
Generated on August 28, 2008, 12:03 am



© 2007 Lionel Brits

This page validates as XHTML 1.1 RSS 2.0 posts


Theme © 2006 Lionel Brits