left join cannot access main table fields

Question

I want to find the latest date from b.filed that matches the a.id from table b, and then add this column to the result set.

Table a

Table b

Expected result

Try

SELECT
    a.id,
    a.NAME,
    b.date
FROM
    a
LEFT JOIN LATERAL (
    SELECT * FROM b WHERE b.id = a.id ORDER BY date DESC LIMIT 1
    ) AS b
ON b.id = a.id

But my MySQL version is 5.7, which does not support LATERAL, so I used

SELECT
    a.id,
    a.NAME,
    (   SELECT b.date FROM b WHERE a.id = b.id ORDER BY b.date DESC LIMIT 1
    ) AS date 
FROM
    a

You can get expected values using max and group by by only querying second table — Selvin, Oct 25 '22 at 07:02
'The actual situation is that I need to access many b fields, so writing b.field1,b.field2,b.field3 is garbage code' - Question looks oversimplified ,you should publish **representative** sample data — P.Salmon, Oct 25 '22 at 07:19
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [mre] PS What is the question? — philipxy, Oct 25 '22 at 07:57
[Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) — philipxy, Oct 27 '22 at 01:31

Jonas Metzler · Answer 1 · 2022-10-25T07:26:24.050

I still don't understand which purpose your table A has. In my opinion, it's sufficient to use table B in your query. So this query will do:

SELECT id, name, MAX(yourdate) AS date
FROM table_b GROUP BY id,name;

Anyway, if you want to join table a, you can do it.

SELECT b.id, b.name, MAX(b.yourdate) AS date
FROM table_b AS b JOIN table_a AS a ON a.id=b.id 
GROUP BY b.id,b.name;

You can verify here this is also working correctly: db<>fiddle

In the second query, change JOIN to LEFT JOIN if it's intended to also show those rows from table B whose id DO NOT match the id in table A.

left join cannot access main table fields

1 Answers1