左连接MySql

Question

我一直在关注这个左连接答案，但是我的答案不能工作。

MYSQL Left Join last row of result

我有两张桌子

leicester

count   area_id  section   toberth
  1        LR       WL       300
  2        LR       WL       301
  3        ER       SN       300


berthmove

count   area_id    timemove    description    toberth
  1        LR        124          6R43           300
  2        LR        126          5RT4           300
  3        TR        128          6TF7           310
  4        LR        121          6TT3           301
  5        LR        122          5RR7           301
  6        TR        127          7YY9           300

以下是我的代码

  $stmt=$mysql_link->prepare("SELECT  l.*,b.* 
     FROM leicester AS l
     LEFT JOIN berthmove AS b ON l.toberth=b.toberth WHERE l.area_id=b.area_id AND l.section="WL"
     LEFT JOIN berthmove AS b2 ON l.toberth=b2.toberth AND b.timemove<b2.timemove
     WHERE b2.timemove is NULL LIMIT 1 ");      
     $stmt->execute();  

我试图以描述5RT4和5RR7结束，即berthmove表中的第2行和第5行。即从'leicester‘表中选择300号和301号泊位(即它们在WL区)，然后找到与300和301相对的描述。但它只能是每一个的最新描述(基于timemove)。还需要检查area_id是否相同。

请帮个忙

这就是取物

   foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $berths)
     {
     echo "hello";
     $selectberth=$berths['toberth'];
     $description=$berths['descr'];
     echo $description;
     echo $selectberth;
     }

编辑

更新为代码以反映对WHERE命令和解析问题的更改

     $stmt=$mysql_link->prepare("SELECT  l.*,b.* 
     FROM leicester AS l
     LEFT JOIN berthmove AS b ON l.toberth=b.toberth
     LEFT JOIN berthmove AS b2 ON l.toberth=b2.toberth AND b.timemove<b2.timemove
     WHERE l.area_id=b.area_id AND l.section='WL' AND b2.timemove is NULL LIMIT 1 ");       
     $stmt->execute();      

Answer 1

尝试使用子查询，然后使用内部连接来查找所有相关信息，而不是通过连接berthmove来查找每个组的最大值为timemove的记录：

SELECT l.*, b2.*
FROM leicester l
INNER JOIN 
(SELECT area_id, toberth, max(timemove) as timemove
    FROM berthmove
    GROUP BY area_id, toberth) AS b1
ON l.area_id = b1.area_id AND l.toberth = b1.toberth
INNER JOIN berthmove b2
ON b1.area_id = b2.area_id
AND b1.toberth = b2.toberth
AND b1.timemove = b2.timemove
WHERE l.section = 'WL';