Need help fixing SQL query, that works, but weirdly

Other SQL Server Topics
1 
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!).

2

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
3

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.

4

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.

5

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
6

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.

7

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
8

YES! Thanks. That's it.