MySql Left Join with subselect
14,119
Solution 1
Put the second condition in the on
clause. This method does it as a correlated subquery:
SELECT u.*, i.infotext
FROM user u LEFT JOIN
infos i
ON u.id = i.id_user and
i.insert_time = (SELECT MAX(i2.insert_time)
FROM infos i2
WHERE i2.id_user = i.id_user
);
Solution 2
If performance is key...
SELECT u.id
, u.name
, u.adress
, i.infotext
FROM user u
LEFT
JOIN
( SELECT x.*
FROM infos x
JOIN
( SELECT id_user
, MAX(insert_time) insert_time
FROM infos
GROUP
BY id_user
) y
ON y.id_user = x.id_user
AND y.insert_time = x.insert_time
) i
ON i.id_user = u.id
ORDER
BY infotext IS NULL, infotext;
Author by
user3362088
Updated on June 04, 2022Comments
-
user3362088 almost 2 years
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 | +----+--------+----------+----------+