How to get last added field from MySQL database JOIN

Question

My database has two tables

MariaDB [testnotes]> describe contactstbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(6)      | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| email | varchar(40) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

MariaDB [testnotes]> describe notestbl;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| id        | int(6)   | YES  |     | NULL    |       |
| notes     | blob     | YES  |     | NULL    |       |
| dateadded | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+

I want a query that will show the last notes in the notestbl table for the give ID

contactstbl has about 100ish records I want to show them all even without notes

MariaDB [testnotes]> select * from contactstbl;
+------+------+-------+--------+
| id   | name | phone | email  |
+------+------+-------+--------+
|    1 | fran | 12335 | gf@g.m |
|    2 | tony | 45355 | ck@g.m |
|    3 | samm | 46545 | fs@g.m |
+------+------+-------+--------+

MariaDB [testnotes]> select * from notestbl;
+------+------------------+---------------------+
| id   | notes            | dateadded           |
+------+------------------+---------------------+
|    1 | 2 days ago notes | 2020-01-12 00:00:00 |
|    3 | 5 days ago notes | 2020-01-09 00:00:00 |
|    3 | 3 days ago notes | 2020-01-11 00:00:00 |
|    1 | 1 days ago notes | 2020-01-13 00:00:00 |
|    1 | 3 days ago notes | 2020-01-11 00:00:00 |
+------+------------------+---------------------+
5 rows in set (0.00 sec)

I have tried a couple different queries and just cannot seem to get it right.

SELECT c.id,c.name,c.email,n.id,n.dateadded,n.notes FROM contactstbl c left join notestbl n using(id) GROUP BY c.id ORDER BY n.dateadded ASC;

Which is very close.

+------+------+--------+------+---------------------+------------------+
| id   | name | email  | id   | dateadded           | notes            |
+------+------+--------+------+---------------------+------------------+
|    2 | tony | ck@g.m | NULL | NULL                | NULL             |
|    3 | samm | fs@g.m |    3 | 2020-01-09 00:00:00 | 5 days ago notes |
|    1 | fran | gf@g.m |    1 | 2020-01-12 00:00:00 | 2 days ago notes |
+------+------+--------+------+---------------------+------------------+

What I want to see is:

+------+------+--------+------+---------------------+------------------+
| id   | name | email  | id   | dateadded           | notes            |
+------+------+--------+------+---------------------+------------------+
|    2 | tony | ck@g.m | NULL | NULL                | NULL             |
|    3 | samm | fs@g.m |    3 | 2020-01-11 00:00:00 | 3 days ago notes |
|    1 | fran | gf@g.m |    1 | 2020-01-13 00:00:00 | 1 days ago notes |
+------+------+--------+------+---------------------+------------------+

score 0 · Answer 1 · answered Jan 14 '20 at 14:37

SELECT C.ID,
       C.NAME,
       C.EMAIL,
       N1.ID,
       N1.DATEADDED,
       N1.NOTES
FROM CONTACTSTBL C
LEFT JOIN NOTESTBL N1 USING(ID)
LEFT JOIN NOTESTBL N2 ON N1.ID = N2.ID
AND N1.DATEADDED < N2.DATEADDED
WHERE N2.ID IS NULL
ORDER BY N1.DATEADDED;

also try some ideas from here how do I query sql for a latest record date for each user

score 0 · Accepted Answer · answered Jan 14 '20 at 14:44

Just use subquery in SELECT clause:

SELECT 
    c.id,
    c.name,
    c.email,
    (SELECT n.id FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nid,
    (SELECT n.dateadded FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) ndateadded,
    (SELECT n.notes FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nnotes
FROM 
    contactstbl c 
GROUP BY c.id 
ORDER BY ndateadded ASC;

Result:

MariaDB [test]> SELECT
    ->     c.id,
    ->     c.name,
    ->     c.email,
    ->     (SELECT n.id FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nid,
    ->     (SELECT n.dateadded FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) ndateadded,
    ->     (SELECT n.notes FROM notestbl n WHERE n.id=c.id ORDER BY n.dateadded DESC LIMIT 1) nnotes
    -> FROM
    ->     contactstbl c
    -> GROUP BY c.id
    -> ORDER BY ndateadded ASC;
+----+------+--------+------+---------------------+------------------+
| id | name | email  | nid  | ndateadded          | nnotes           |
+----+------+--------+------+---------------------+------------------+
|  2 | tony | ck@g.m | NULL | NULL                | NULL             |
|  3 | sam  | fs@g.  |    3 | 2020-01-11 00:00:00 | 3 days ago notes |
|  1 | fran | gf@g.m |    1 | 2020-01-13 00:00:00 | 1 days ago notes |
+----+------+--------+------+---------------------+------------------+
3 rows in set (0.07 sec)

ev350 · Answer 3 · 2020-01-14T15:04:54.757

First, I think that you should change the schema of your notestbl table as it doesn't have its own id field, but instead relies purely on the id of the contactstbl table. This is bad design and should be normalised so as to prevent you pain in the future :)

I'd recommend it is changed to something like this:

mysql> select * from notestbl;
+------+------------+------------------+---------------------+
| id   | contact_id | notes            | dateadded           |
+------+------------+------------------+---------------------+
|    1 |          1 | 2 days ago notes | 2020-01-12 00:00:00 |
|    2 |          3 | 5 days ago notes | 2020-01-09 00:00:00 |
|    3 |          3 | 3 days ago notes | 2020-01-11 00:00:00 |
|    4 |          1 | 1 days ago notes | 2020-01-13 00:00:00 |
|    5 |          1 | 3 days ago notes | 2020-01-11 00:00:00 |
+------+------------+------------------+---------------------+
5 rows in set (0.00 sec)

Then you can use this single line query to get the result you're after:

select c.id, c.name, c.email, n.id, n.dateadded, n.notes from contactstbl c left join (select t1.id, t1.contact_id, t1.dateadded, t1.notes from notestbl t1, (select contact_id, max(dateadded) as maxdate from notestbl group by contact_id) t2 where t1.contact_id=t2.contact_id and t1.dateadded=t2.maxdate) n on c.id=n.contact_id;

+------+------+--------+------+---------------------+------------------+
| id   | name | email  | id   | dateadded           | notes            |
+------+------+--------+------+---------------------+------------------+
|    1 | fran | gf@g.m |    4 | 2020-01-13 00:00:00 | 1 days ago notes |
|    2 | tony | ck@g.m | NULL | NULL                | NULL             |
|    3 | samm | fs@g.m |    3 | 2020-01-11 00:00:00 | 3 days ago notes |
+------+------+--------+------+---------------------+------------------+
3 rows in set (0.00 sec)

A more visually pleasing representation of the query:

select  c.id, 
        c.name, 
        c.email, 
        n.id, 
        n.dateadded, 
        n.notes 
from contactstbl c 
left join   (select t1.id, 
                    t1.contact_id, 
                    t1.dateadded, 
                    t1.notes 
            from    notestbl t1, 
                    (select contact_id, max(dateadded) as maxdate from notestbl group by contact_id) t2 
            where   t1.contact_id=t2.contact_id 
                and t1.dateadded=t2.maxdate) n 
on c.id=n.contact_id;

How to get last added field from MySQL database JOIN

3 Answers3