0

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?

pong
  • 514
  • 4
  • 12
  • 1
    If you really don't want to use a subquery, then you can use a LEFT JOIN as described in the manual (see the manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html), but it will be significantly slower than the uncorrelated query used as the accepted answer in the 'duplicate' link – Strawberry Nov 18 '16 at 18:08

1 Answers1

1

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

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172