MySQL Gives results for every row in JOINed table

Question

I have been trying to perform a query with a JOIN to enhance performance. Currently, one query would be done to retrieve all records, then for each record another query is performed to grab the specific step completed.

I have two tables, that look like this:

Users:

id - name - email
------------------
1 - alex - a@b.com

Steps:

id - userId - step - date
-------------------------------
1  -   1    -   1  - 2018/01/01
2  -   1    -   2  - 2018/02/02
3  -   1    -   3  - 2018/03/03

Now the query I use to grab all users and their respective steps is:

SELECT u.id, u.name, u.email, s.step, s.date
FROM users u
LEFT JOIN steps s ON s.userId = u.id

But this would return:

id - name - step - date
-------------------------------
1  -  alex  -   1  - 2018/01/01
2  -  alex  -   2  - 2018/02/02
3  -  alex  -   3  - 2018/03/03

And I am only interested in the last result, I believe I should be using a subquery perhaps of some kind? And the MAX(s.date) function?

score 1 · Accepted Answer · answered Jul 17 '18 at 16:46

1

You are correct

Where steps.id IN (select 
MAX(id) from 
steps group by user userid)

This assumes your MAX(id) in steps will always be the greatest date.

answered Jul 17 '18 at 16:46

Bleach

561
4
11

Do I add this to the bottom of my existing query? – Alex Jul 17 '18 at 17:06
Yes. Add to the bottom – Bleach Jul 17 '18 at 18:02
Thanks, sorry I got caught up in something else. But I managed to get it working! – Alex Jul 17 '18 at 19:30

MySQL Gives results for every row in JOINed table

1 Answers1