SQL query gone wrong

Databases
1

http://pastebin.com/RpUDmTjR

Is what I have. The result?

It should be 7 rows. Going from ID’s, 1, 2, 3, 4, 5, 6, 7.

What I have instead are ID’s 2, 2, 6, 7, 1, 5, 6, 5, 5, 5, 2, 4, 4, 7 and so on.
Not to mention that they spawn incorrect results (talking about lastTopicAuthorId and lastTopicAuthorName).
Anything “forums” related, seems fine.

2

SELECT topics.author lastTopicAuthorId , members.username lastTopicAuthorName , forums.id forumId , forums.name forumName , forums.description forumDescription , forums.lastTopicId forumLastTopicId , ( SELECT COUNT(*) FROM topics WHERE topics.parent = forums.id ) forumTopicCount FROM forums LEFT OUTER JOIN topics ON topics.parent = forums.id LEFT OUTER JOIN members ON members.id = topics.author

your problems are a bit more obscure than you may realize

if a forum has multiple topics, your results will have one row per topic per forum

and each of those topic rows will have a column called lastTopicAuthorId

but there’s nothing to decide which one is the MAX

3

Before, I had this:

													members.id lastTopicPosterId,
													members.username lastTopicPosterName,
													forums.id forumId,
													forums.name forumName,
													forums.description forumDescription,
													forums.lastTopicId forumLastTopicId,
													(SELECT COUNT(*) FROM topics WHERE topics.parent = forums.id) forumTopicCount,
													topics.id forumLastTopicId
												  FROM forums
												  	LEFT OUTER JOIN members ON members.id = forums.lastTopicId
													LEFT OUTER JOIN topics ON forums.lastTopicId = topics.id```

and everything worked out. I just made couple changes and it exploded :|

Also, there's no difference between what you've written and what I had.
4

other than legibility :wink:

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.