Need help with a join query

[LuckyTaxi]

I need to get the username associated with a post. There are multiple tables involved.

Table user_messages
================
message_id
user_id
message
response_message_id

Table users
============
user_id
user_nickname

Example Data:
message_id | user_nickname | message | response_message_id |
| 1 | bob | I'm bored | NULL |
| 2 | bob | great plains is awful | NULL |
| 3 | susan | someone is hogging the network | NULL |
| 4 | susan | IT is slacking off again! | 2 |



Let's take message_id #4 and let's display it as:

Susan (response to Bob) - "IT is slacking off again!"

Notice the response_message_id column is a reference to message_id # 2 which belongs to Bob.

 

[Markbnj]

I don't understand your query. What is IQ and why are you comparing it to a character string?

 

[Fallen Kell]

ummm....

"SELECT user_nickname.users, message.user_messages FROM users, user_messages WHERE user_id.user_messages=user_id.users;"

The above will give you the user name along with the associated message that the user posted for all messages in the user_messages table.

Now if you want to get the reply username, well that gets more complicated, especially if you want to list them all out and list the reply to in your current design. Personally I would add a user (user_id=0, user_nickname="none") and alter your user_messages table

ALTER TABLE user_message ADD INDEX (user_id);
ALTER TABLE user_message ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(user_id);

This is to tell the database that user_id.user_message is actually a link to user_id.users and it will keep you from inserting a value into that which is not a valid value in the users table.

ALTER TABLE user_messages CHANGE response_message_id <whatever you used> NOT NULL DEFAULT 0;

This will make dealing with responses easier if you want to simply print the stuff out. With the NULL value, it is a lot harder to do the joins of the tables because you will not be able to join with the NULL. Adding the user_id=0 will now join to a user named "none", which will work fine for the query and you can post process string match to deal with the response to "none".

ALTER TABLE user_message ADD INDEX (response_message_id);
ALTER TABLE user_message ADD CONSTRAINT FOREIGN KEY (response_message_id) REFERENCES users(user_id);

Again, the above will tell the database engine that the user_message.user_id field is actually a value that is from the users (that is what the FOREIGN KEY thing is). The same goes with the response_message_id. However, if I was designing this for something like a forum, I wouldn't point to the USER that I am responding to, but I would point to the MESSAGE that I am responding to, which would in this setup be the message_id, which would itself contain the user that said it. This way you know where to "thread" the messages together and keep conversations of a single topic together.


Now unless I am misunderstanding how you are explaining it to me. If in fact response_message_id is a link to the message_id of another message, well that is good. Don't do the ALTER TABLE on the users table to change the user_id field. And don't do the FOREIGN KEY on the response_message_id. And you would need to change your query to get the "nickname" that a message is responding to by doing multiple queries, or a sub-select.

 

[Fallen Kell]

Oh, one last thing about the user_id="0", you need to have certain options turned on to do that. It might just be easier to have a (user_id="something", user_nickname="none"). You might also want to consider creating a temporary table especially if you are going to be doing a lot of "SELECTS" for things like you pointed out. You may also want to consider having this table use the MEMORY engine especially if this is for a site that will get accessed a lot, but read up on that before you try it since there are some potential problems that can occur.

 

[Kntx]

I think this should do it. If not there's probably just a few syntax things wrong.


edit: somehow the code attach got messed up.



SELECT


u.user_nickname,
ru.user_nickname AS responding_to_user_nickname,
m.[message],


u.user_nickname +
CASE
WHEN rm.message_id IS NULL
THEN ''
ELSE ' (response to ' + ru.user_nickname + ')'
END +
' - "' + m.[message] + '"' AS a_big_ugly_string_the_database_should_not_be_creating

FROM user_messages m
INNER JOIN users u ON m.[user_id] = u.[user_id]
LEFT JOIN user_messages rm ON m.[response_message_id] = rm.[message_id]
LEFT JOIN users ru ON rm.[user_id]= ru.[user_id]

 

[Kntx]

P.S. great plains doesn't suck!! <3 great plains.