SQL limitation... workaround?

[Anon]

This may only be a limitation of MySQL, but I think there are similar issues across platforms. I know that in most SQL databases a sub-select could solve this problem, but MySQL isn't implementing those until version 4.

I'm building a message board application, and I am wanting to view the author of the last reply. Originally, I had decided to simply keep the ID of the last reply in the message, but I am also implementing an ignore feature - so the ID would change depending on who is being ignored. For that reason, I want to use SQL to retrieve the row and the user's info. example:

SELECT message.* MAX(message.messageId) as lastReplyId, message2.username from message left join message as message2 on (message2.messageId=lastReplyId)

but, as you probably know, this does not work. Is there a workaround for this that doesn't involve doing a separate query for each individual row?

[Anon]

sorry, I forgot the second half of the query. It would be more like this:

"SELECT message.* MAX(message.messageId) as lastReplyId, message2.username as lastReplier from message left join message as message2 on (message2.messageId=lastReplyId) where lastReplier not in ('userlist') group by message.topicId

[[deleted]]

This query is bogus, but not because it uses a subwuery (because it doesn't)

You cannot select MAX() alongside 'non agfgregates' without a GROUP BY clause.

I think you need to rethink your entire strategy.

[Anon]

did you even read my question?

I am not asking about the syntax of the query; I know the query doesn't work; if it worked, I wouldn't be here. The query was not a specific query that I was trying to make work, but rather an example of the idea I am trying to accomplish. Also, my second post was a more complete query (which included a GROUP BY clause). Do you know of a way to accomplish this, or not?

[[deleted]]

"did you even read my question? "

did you even ask a question?

"I know the query doesn't work; if it worked, I wouldn't be here."

Well hey ho, I never would have guessed. Sorry for trying to explain why it doesn't work.

Your first post was unclear to say the least. Your second second post did include a group by, sorry for missing that.

Try explaining your question again, your problem may be very clear to you because you have been working with it for some time. For me and the others here, all we have is what you post, and apperently we don't understand what you mean.

[Anon]

okay. I'm sorry you didn't understand my question. I'm frustrated because I -am- trying to rethink my strategy and came here for help in that, only to be told nothing other than what I already knew. After rereading it it still seems clear to me, but I will try rephrasing. I apologize for whatever lack of proper terminology is contributing to my inability to communicate my problem to you.

I have two tables. one of them contains messages, the other one contains topics. There are many messages associated with one topic. Thus, the message table has a message ID, a topic ID with which the message is associated, and some other columns (the information I am interested in is the author ID). The topic table has a topic ID, which is the same as the first message ID in that topic (a primary key that will allow me to get the author of the original message), and also some other information.

I would like, in addition to retrieving the author of the original message, to retrieve the author of the last reply. Originally, every time a new message was added to a topic, I updated a "last reply ID" column in the database. However, I am adding an ignore feature, in which case the last reply to the thread is not necessarily the last one that a user will want to view. So, I need to select the last reply with a qualified query.

The problem is, as you know, that MySQL does not yet allow sub-selects, and it does not allow the syntax I demonstrated in my second post (and not just because of the missing comma). It fails to recognize lastReplyId as a value at that point, because at the time the query is run, the aggregate functions have not completed their calculations.

So what I want to know is: is there a way, without doing one query per result row, to retrieve the data about the last replies of each of these topics?

[smarlowe]

I'm not sure how to do it in MySQL, but I'm guessing some kind of strange join may be able to do it. You are making me quite glad I use postgresql right now.

[Anon]

well, I guess no one can answer it. the only workaround I could find was particularly tedious.

basically, I iterated through my initial result set, copying the IDs of the author column into an array. then, I called implode() to create a comma-delimited list. Then I ran an SQL query to retrieve the data I was interested in. Unfortunately, though, it then came back in a different order than the first query, so i had to also iterate through THAT result set and build another array that my script could access.

Conclusion: MySQL really needs sub-selects.