Join a sql query with another table

348
January 03, 2017, at 00:35 AM

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

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.

Answer 2

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.

Rent Charter Buses Company
READ ALSO
Database Design: Primary key and many to many associations

Database Design: Primary key and many to many associations

This is my first database design of my home libraryI have a question about how to implement the primary keys in each table, I am also interested to know if it is acceptable to have many tables associated with one foreign as I have with the many relationships...

240
Deploying a system [on hold]

Deploying a system [on hold]

i have created a really simple data browsing and manipulating system (more like a logbook system, just add, edit, delete, save, and a search function as well) in vbnet 2010 and MySQL with XAMPP

301
MySQL storing TIMESTAMP with milliseconds

MySQL storing TIMESTAMP with milliseconds

I need to create a database to store some logs which can occurs once per millisecondI've created the following table:

533