April 10th, 2017, 04:30 PM
Retrieving columns and corresponding row values after joining two tables
I am getting familiar with MYSQL and I have hoping someone can point me in the right direction. I have two tables (listed below). I have a messaging system and I am trying to receive the latest thread that each user is involved with. What I am trying to accomplish is to retrieve the thread-id corresponding to the max(id) of Table 1 which is the latest entry. However, I also need this to correspond with the user_id in Table 2. Both tables share a common variable (thread_id), however the variable "id" in both Tables is not the same variable.
This is what I have so far:
For example, if var USERID = 1, I should retrieve 85 as the thread_id. However, if USERID = 31, I should retrieve 25 as the thread_id.
"SELECT thread_id FROM Table1 FULL JOIN Table 2 ON Table1.thread_id = Table2.thread_id WHERE Table1.id = (SELECT max(id) FROM Table1) AND user_id = %d", USERID
id thread_id user_id
320 123 1
169 85 1
170 85 22
49 25 22
50 25 31
I would really appreciate anyone who can tell me why my query isn't retrieving anything.
April 10th, 2017, 04:32 PM
Please excuse the topic name. I submitted it by accident. I read your sticky post and I understand your topic name policy.
April 10th, 2017, 10:25 PM
No worries. A moderator can edit the title for you. What did you want it to say?
1. Why should the first one get 85? 85 and 123 are both in Table1 so the highest one would be 123.
2. You shouldn't rely on the ID to decide how recent a thread is - the ID should just be a number with no significant meaning beyond that. Is there a date available you can use instead?
April 10th, 2017, 10:40 PM
I wound up with this statement which seems to be working for me.
I forgot what I was going to name the title but it could have been something like "Retrieving columns and corresponding row values after joining two tables". You can update it if you still feel it necessary.
"SELECT Table1.thread_id FROM Table1 RIGHT JOIN Table2 ON Table1.thread_id = Table2.thread_id WHERE Table2.user_id = %d ORDER BY Table1.id DESC Limit 0,1"
1) I was looking to get the thread id from table 1 with the highest id from Table 1 but that also corresponds with the user from Table 2. Therefore user 1 should return thread 85 because thread 85 has a higher id and is therefore more recent.
2)I am relying on ID because it is incremented and updated whenever there is a new message in the thread. I do have a date column as well. Do you suggest that would cause less error?
April 11th, 2017, 03:01 AM
based on what you've posted, you want an INNER join -- not a RIGHT JOIN, and not a FULL JOIN
and yes, use a datetime column instead of id to determine latest