jeudi 24 novembre 2016

MySql Left Join with subselect

I have 2 tables (user and infos). I need to select all user data and the related last inserted 'infotext' (insert_time)

table user

+----+--------+----------+
| id | name   | adress   |
+----+--------+----------+
|  1 | Name 1 | Adress 1 |
|  2 | Name 2 | Adress 2 |
|  3 | user 3 | adress 3 |
|  4 | user 4 | adress 4 |
+----+--------+----------+

table infos

+----+---------+----------+---------------------+
| id | id_user | infotext | insert_time         |
+----+---------+----------+---------------------+
|  1 |       1 | info 1   | 2016-11-24 14:03:23 |
|  2 |       1 | info 2.  | 2016-11-24 14:08:30 |
|  3 |       3 | text 3.  | 2016-11-24 14:08:46 |
+----+---------+----------+---------------------+

My current query is:

SELECT  a.*, b.infotext FROM    user a LEFT JOIN infos b
            ON a.id = b.id_user
        LEFT JOIN
        (
            SELECT      id_user, MAX(insert_time) newestInsert
            FROM        infos
            GROUP BY    id_user
        ) c ON  c.id_user = b.id_user AND
                c.newestInsert = b.insert_time

But the problem is it outputs the id not distinct:

+----+--------+----------+----------+
| id | name   | adress   | infotext |
+----+--------+----------+----------+
|  1 | Name 1 | Adress 1 | info 1   |
|  1 | Name 1 | Adress 1 | info 2.  |
|  3 | user 3 | adress 3 | text 3.  |
|  2 | Name 2 | Adress 2 | NULL     |
|  4 | user 4 | adress 4 | NULL     |
+----+--------+----------+----------+

The final result I need is:

+----+--------+----------+----------+
| id | name   | adress   | infotext |
+----+--------+----------+----------+
|  1 | Name 1 | Adress 1 | info 2.  |
|  3 | user 3 | adress 3 | text 3.  |
|  2 | Name 2 | Adress 2 | NULL     |
|  4 | user 4 | adress 4 | NULL     |
+----+--------+----------+----------+

Aucun commentaire:

Enregistrer un commentaire