Join a sql query with another table

Question

I am using the following query to get recent most record of a user.

SELECT t1.username, t1.value, t1.date
FROM table t1
JOIN (select username, max(date) as maxdate from table
      group by username) t2 on t1.username = t2.username
                           and t1.date = t2.maxdate
WHERE t1.date >= CURDATE()
ORDER BY t1.date DESC

Its returning a table of following structure

------------------------
username | value | date
------------------------

I have another table (t3) of following structure

----------------------------
username | category | group
----------------------------

How can I get a result similar to following table structure

-------------------------------------------
username | value | date | category | group
-------------------------------------------

How to join these relations?

Answer 1

Although a direct join is the most effective and fast way, if you'd do an operation such as max(date) on your t1 select statement (such as in your t2 select), you could construct the query in a similar way as you've done on the t2 select query:

SELECT maintable.username, maintable.value, maintable.date, secondtable.category, secondtable.group
FROM (
    SELECT t1.username, t1.value, t1.date
    FROM table t1
    JOIN (select username, max(date) as maxdate from table
          group by username) t2 on t1.username = t2.username
                               and t1.date = t2.maxdate
    WHERE t1.date >= CURDATE()
    ORDER BY t1.date DESC
    ) maintable
JOIN table secondtable ON maintable.username = secondtable.username

Note that this is in no way the best option if you're not creating a operation on the inner select.

Answer 2

You can JOIN it the follwing way:

SELECT t1.username, t1.value, t1.date, t3.category, t3.group
FROM table t1
JOIN (select username, max(date) as maxdate from table
      group by username) t2 on t1.username = t2.username
                           and t1.date = t2.maxdate
JOIN table3 t3 ON t3.username=t1.username
WHERE t1.date >= CURDATE()
ORDER BY t1.date DESC

Hint: I do not know mysql but I think it's the same in MS SQL.