[Solved]
Solution
I’m not sure how to explain what I did, but I joined the data in Table 2 to Table 1, instead of joining the data from Table 1 to Table 2, and it worked.
SELECT a.status, a.date, b.acct, b.name
FROM t2 a
INNER JOIN t1 b ON b.acct = a.acct
WHERE a.date =
(
SELECT c.acct
FROM t2 c
WHERE c.date =
(
SELECT MAX(date)
FROM t2
WHERE acct = c.acct
)
)
I’m using MySQL 5.7
Table 1 (t1)
acct = PK
acct | name
---------------
12345 | wade |
67890 | james |
Table 2 (t2)
acct | status | date
-----------------------------
12345 | ready | 2018-02-10
67890 | ready | 2018-02-10
12345 | complete | 2018-02-11
67890 | complete | 2018-02-11
I want to get the most recent record (or last record) for each account from Table 2, and then join that data to Table 1.
There are only 2 rows in Table 1, and if you run the subquery separate it returns only 2 rows, so I’m confused why 4 rows are being returned. The result set below should only show the complete records for each account.
SELECT a.acct, a.name, b.status, b.date
FROM t1 a
INNER JOIN t2 b ON b.acct = a.acct
WHERE a.acct IN
(
SELECT c.acct
FROM t2 c
WHERE c.date =
(
SELECT MAX(date)
from t2
WHERE acct = c.acct
)
)
Result set
acct | name | status | date
-----------------------------
12345 | wade | ready | 2018-02-10
12345 | wade | complete | 2081-02-11
67890 | james | ready | 2018-02-10
67890 | james | complete | 2018-02-11