pong pong - 14 days ago 7
MySQL Question

MySQL: JOIN tables and return a column from a row depending on an aggregate function on another column

Let there be two tables:


  • Table A

    id | name
    1 x
    2 y

  • Table B

    foreign_key | value | external
    1 1 60
    1 2 50
    2 3 80
    2 4 90



The desired result is a
JOIN
looking like this:

id | name | external
1 x 50
2 y 90


i.e., for each row in
A
we get the corresponding
external
from
B
where
value
is max for a given
id
.

What I have so far is this:


SELECT
A.`id`,
A.`name`,
B.`external`
FROM `A`
LEFT JOIN `B`
ON A.id = B.foreign_key
GROUP BY id


This obviously returns the first
B.external
encountered instead of the one with the highest
value
:

id | name | external
1 x 60
2 y 80


Is there a way to achieve this, preferably without using subqueries?

Answer

Not sure why dont want sub-query but Correlated sub-query looks simpler to me

select id, name,
      (Select external 
       from TableB B where A.id = B.foreign_key Order by Value desc Limit 1 ) 
From TableA A

If you want to achieve this using JOIN then you may have to join the TableB twice

Comments