Select MAX value inside a JOIN and JOIN inside another SELECT

Question

I have two tables:

table a:

PersonID | name | age | gender  
-------- | ---- | --- | ------  
20       | John | 45  | Male  
30       | Ann  | 50  | Female

table b:

ValueID | PersonID | Column1   
------- | -------- | -------  
1       | 20       | 654321  
2       | 20       | 345653  
3       | 30       | 756756  
4       | 30       | 142857

I need to get all values from table 'a' and for each one a corresponding Column1 with a.PersonID = b.PersonID and with the MAX ValueID.

The expected result in this example would be the following:

 PersonID | name | age | gender | Column1
 -------- | ---- | --- | ------   -------
 20       | John | 45  | Male   | 345653  
 30       | Ann  | 50  | Female | 142857

I tried this and doesn't work.

SELECT ps.*, 
(SELECT Column1 FROM b ef  
JOIN (SELECT MAX(ValueID) AS id FROM b  
  WHERE PersonID = ps.PersonID) max ON ef.PersonID = max.id ) AS Column1  
FROM a ps

I guess ps.PersonID in WHERE clause ef.PersonID = ps.PersonID is not recognized.

I appreciate very much your help.

@Strawberry simple english please.. as far as i know it should fix the issue — RoMEoMusTDiE, Jun 08 '17 at 22:46
I'm sorry but I don't see this is duplicated. PersonID and ValueID are unique. I cannot GROUP BY any of them. I haven't been able to find a solution yet. Thanks for your help — Agh, Jun 09 '17 at 02:27
Looks like an exact duplicate to me `SELECT a.*,x.valueid,x.column1 FROM tablea a JOIN tableb x.* ON x.personid = a.personid JOIN ( SELECT personid , MAX(valueid) valueid FROM tableb GROUP BY personid ) y ON y.personid = x.personid AND y.valueid = x.valueid` — Strawberry, Jun 09 '17 at 05:42

RoMEoMusTDiE · Answer 1 · 2017-06-08T22:30:28.587

0

Try This.. presumably the valueid is unique/ identity seeded

SELECT ps.*, 
(SELECT Column1 FROM b 
where b.valueId =(SELECT MAX(ValueID) FROM b as x
  WHERE x.PersonID = ps.PersonID)
) AS Column1  
FROM a ps

edited Jun 08 '17 at 22:30

answered Jun 08 '17 at 22:24

RoMEoMusTDiE

4,739
1
17
26

mysql keeps saying: " Unknown column 'ps.PersonID' in 'where clause' " – Agh Jun 09 '17 at 02:22
can you post your query here – RoMEoMusTDiE Jun 09 '17 at 02:28
My query is the one I posted before. My real query is exactly, just changes the names of the fields. I have edited and now is correct. Thanks! – Agh Jun 09 '17 at 02:53

Ry.the.Stunner · Answer 2 · 2017-06-14T21:01:36.603

-1

Try this:

SELECT ps.*, 
  (SELECT Column1 
   FROM b ef  
   JOIN (SELECT MAX(ValueID) AS id FROM b  
   WHERE PersonID = ps.PersonID) max ON ef.PersonID = max.id ) AS Column1  
FROM a ps

edited Jun 14 '17 at 21:01

answered Jun 08 '17 at 22:26

Ry.the.Stunner

22
7

max is based on the column valueid to get the column1 value – RoMEoMusTDiE Jun 08 '17 at 22:27
Oh, I see what you mean now. – Ry.the.Stunner Jun 08 '17 at 22:30
That's right @maSTAShuFu – Agh Jun 09 '17 at 02:28

Select MAX value inside a JOIN and JOIN inside another SELECT

2 Answers2