SQLTeam.com | Weblogs | Forums

Need help fixing SQL query, that works, but weirdly

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

Is what I have. The result?

It should be 7 rows. Going from ID's of forums, 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; they're different too!).

I don't see any logic that defines the "last" Author and/or Topic. Can you shed some light on how you would decide on which ones were the latest?

1 Like

There is a custom field in the database in forums. It points to a thread (see forums.lastTopicId forumLastTopicId). And in topics there's a field called author, which contains ID of the user, and I use this ID to get his/her username.

The "last" topic is defined by custom field in column in forums.
The "last" author is defined by current-pointer column author in the "last" topic table. "[This] forum's last topic ID is [this], [this] topic ID's author is [this], and [this] member ID is the same as author of [that] topic".

That's at least how it should work, but results are said above.

I still don't see how you are filtering on that custom field(s). If it is a BIT field, I'd expect to see something like "WHERE forums.lastTopicId = 1". Since I don't see where you are limiting the results to only the last topic, I'm anticipating that you get all of them which seems to be what you are getting.

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.lastTopicId --<--limit join to LAST topic id
LEFT OUTER JOIN members ON members.id = topics.author

1 Like

I'm terrible at explaining apparently. I will give you real example from database, on how it should reach (keep in mind the query posted above). This shows what I expect it to hit, when scanning through forums, there should be 7 results such as these:

+--------+------------------+----------------------------------------------------+-------------+
| id     | name             | description                                        | lastTopicId |
| int(9) | varchar(24)      | varchar(54)                                        | int(9)      |
| AI     |                  |                                                    |             |
+--------+------------------+----------------------------------------------------+-------------+
| 1      | Proin Sed Turpis | Convallis convallis dolor. Quisque tincidunt pede. | 121         |
+--------+------------------+----------------------------------------------------+-------------+

So I expect to hit the first result (and later every one of them). Select all the data,
then go to topics. And it should search for forums.lastTopicId = topics.id.
Which should hit this:

+--------+---------------------+--------+--------+
| id     | name                | author | parent |
| int(9) | varchar(128)        | int(9) | int(9) |
| AI     |                     |        |        |
+--------+---------------------+--------+--------+
| 121    | Quam Praesent Massa | 208    | 6      |
+--------+---------------------+--------+--------+

Then it should grab author. And go to members and seek for members.id = topics.author.
Which should result in:

+--------+-------------+--------+-------------+---------------------+-----------------+
| id     | avatar      | rank   | username    | password            | email           |
| int(9) | varchar(32) | int(1) | varchar(48) | varchar(128)        | varchar(255)    |
| AI     |             |        |             |                     |                 |
+--------+-------------+--------+-------------+---------------------+-----------------+
| 208    | ai.png      | 1      | 6           | [hexadecimal value] | [base64 rumble] |
+--------+-------------+--------+-------------+---------------------+-----------------+

Which obviously doesn't work.

@ScottPletcher While the query doesn't spam me anymore. And the result is 7 rows.
While using your query (both script and database itself). Say that lastTopicAuthorName and Id are null.
Maybe information above could help you in helping me.

You're joining on "topics.parent" in the query but you state "it should search for forums.lastTopicId = topics.id" in your last comment. So perhaps the join should be:

LEFT OUTER JOIN topics ON topics.id = forums.lastTopicId --<--limit join to LAST topic id

1 Like

YES! Thanks. That's it.