CODEWITHSUNDEEP
mysqlsql
Alphy
Alphy

Reputation: 331

How to join a table to itself and get max row then connect another table to it

I have a table that contains redundant entries. What i need is to get the column with max id data, then connect the table to users table and get name based on the max row user_id

tracking table

 id | labref | user_id
 -----------------------
 1  |   a    |  1
 ------------------------
 2  |   a    |  3
 ------------------------
 3  |   b    |  4
 ------------------------
 5  |   b    |  7
 ------------------------

SQL Query:

 SELECT id,labref,user_id FROM tracking_table t WHERE t.id = 
 (SELECT MAX(t2.id) FROM tracking_table t2, user u WHERE t.labref = t2.labref AND u.id = t2.user_id)

Result:

 id | labref | user_id
 --------------------
 2  |   a    |  3
 --------------------
 5  |   b    |  7
 --------------------

Would like to join users table below

 Users Table

 id  |  name
 -------------
 1  |   ua  
 ------------
 2  |   ub   
 ------------
 3  |   uc  
 ------------
 4  |   ud  
 ------------
 5  |   ue    
 -------------
 7  |   uf   
 -------------

Desired results should be as follows:

  id | labref | name
 --------------------
 2  |   a    |  uc
 --------------------
 5  |   b    |  uf
 --------------------

Suggestions as to where I am now stuck?

Upvotes: 0

Views: 62

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

The users table is unnecessary in the subquery. You need it in the main query:

SELECT t.id, t.labref, u.name 
FROM tracking_table t 
JOIN user u ON u.id = t.user_id
WHERE t.id = (SELECT MAX(t2.id) FROM tracking_table t2 WHERE t.labref = t2.labref);

You shouldn't use comma-separated joins anymore by the way. They were made redundant in 1992. Use explicit ANSI joins instead.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You can use a subquery to find the maximum id value for each lab reference. Then, join to this subquery to leave you with the effective rows you want. Finally, join this to the user table to bring in the usernames.

SELECT
    t1.id,
    t1.labref,
    u.name
FROM tracking_table t1
INNER JOIN
(
    SELECT labref, MAX(id) AS max_id
    FROM tracking_table
    GROUP BY labref
) t2
    ON t1.labref = t2.labref AND
       t1.id     = t2.max_id
INNER JOIN user_table u
    ON t1.user_id = u.id

Output:

enter image description here

Demo here:

Rextester

Upvotes: 2

dns_nx
dns_nx

Reputation: 3943

Please check this statement. Does it fit your needs?

SELECT t.id, t.labref, u.name 
FROM tracking_table t 
INNER JOIN users u 
    on t.user_id = u.id 
WHERE t.id = 
     (SELECT MAX(t2.id) 
      FROM tracking_table t2, user u 
      WHERE t.labref = t2.labref AND u.id = t2.user_id)

Upvotes: 1

Related Questions