#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2017
    Posts
    3
    Rep Power
    0

    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:

    PHP Code:
    "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

    Table1
    :
    id   thread_id 
    1193    85
    1192    85
    1191    25
    1190    85
    1189    123

    Table2
    :
    id   thread_id   user_id
    320     123        1
    169     85         1
    170     85         22
    49      25         22
    50      25         31 
    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.

    I would really appreciate anyone who can tell me why my query isn't retrieving anything.

    Best,
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2017
    Posts
    3
    Rep Power
    0
    Please excuse the topic name. I submitted it by accident. I read your sticky post and I understand your topic name policy.
  4. #3
  5. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,280
    Rep Power
    9645
    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?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2017
    Posts
    3
    Rep Power
    0
    Hello,

    I wound up with this statement which seems to be working for me.
    PHP Code:
    "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" 
    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.

    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?
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,700
    Rep Power
    4288
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo