Interesting many many sql join

Question

I have three related tables: "A (id, val)", "B (id, val)" and a link table with the value "AB (help, bid, val)"

I am asking B to return the values ​​of A, for example:

SELECT A.* 
FROM A INNER JOIN AB ON A.id = AB.aid INNER JOIN B ON AB.bid = B.id
WHERE B.val = 'foo';

Each A has many B, and each B has many A.

And the catch I am falling apart is the need to filter the set so that the query returns rows only when AB.val is maximum for any A / B pair

eg. if i have data:

A

id   val
1    something
2    somethingelse

IN

id   val
1    foo
2    bar

Ab

aid  bid  val
1    1    3
1    2    2
2    1    1
2    2    4

I would like to select only the first and last lines of AB, since they are the maximum values ​​for each of A, and then they can query against B.val = 'foo' to return only the first line. I do not know how I can limit only the max val row in table AB.

The best I could get is

SELECT * 
FROM A 
INNER JOIN 
  (SELECT aid, bid, MAX(val) AS val FROM AB GROUP BY aid) as AB
  ON A.id = AB.aid 
INNER JOIN B ON AB.id = B.id
WHERE B.val = 'foo'

. -, , , -, . , , , , max (val). , , , , - undefined.

, , , . .

( , , , A - , B - . AB - WordPhoneme "". , . ( - , )

Answer 1

, , ab max val a.id.

- :

select a.*
from a
left join (
    select aid, max(val) as val 
    from ab 
    group by aid
) abmax on abmax.aid=a.id
inner join ab on ab.aid=abmax.aid and ab.val=abmax.val
inner join b on b.id=ab.bid
where b.val='foo'

Answer 2

, :

select a.*
from ab
   inner join b on(ab.bid=b.id)
   inner join a on (ab.aid=a.id)
where ab.val = (select max(val) from ab AS ab2 where ab2.aid = ab.aid)
   and b.val='foo'

Answer 3

, :

SELECT a.*
FROM ab AS ab1
  LEFT OUTER JOIN ab AS ab2 ON (ab1.aid = ab2.aid AND ab1.val < ab2.val)
  JOIN a ON (ab1.aid = a.id)
  JOIN b ON (ab1.bid = b.id)
WHERE ab2.aid IS NULL
  AND b.val = 'foo';

, AB . ab1, val. , ab1 .

Answer 4

, sql , MS SQL A, B. , , .

Answer 5

SELECT *
FROM
(
  SELECT
    A.*,
    (SELECT top 1 AB.BID FROM AB WHERE A.AID = AB.AID ORDER BY AB.val desc) as BID
  FROM A
) as Aplus
JOIN B ON Aplus.BID = B.BID