SQL LEFT JOIN，条件为空白或最后一个条目

问：

我正在尝试编写一个查询，该查询将获取每个资产行，并填写空白，表示没有上次审计日期或上次审计日期（如果此资产存在多个审计）。下表为两张;第一个用于资产，第二个用于执行的审计。

查询中还有两个额外的左连接，其中包含另外两个表，但它们是直接的一对一属性映射，并且工作正常，如下面的数组结果所示。

CREATE TABLE `asset_audit` (
    `audit_id` int(16) NOT NULL,
    `audit_asset_id` varchar(16) NOT NULL,
    `audit_date` date NOT NULL,
    `audit_present` varchar(16) NOT NULL,
    `audit_repairs` varchar(16) NOT NULL,
    `audit_comment` varchar(256) NOT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci;

CREATE TABLE `asset` (
    `asset_id` int(32) NOT NULL,
    `type` varchar(64) NOT NULL,
    `supplier` varchar(16) NOT NULL,
    `asset_name` varchar(64) NOT NULL,
    `asset_desc` varchar(128) NOT NULL,
    `qty` int(32) NOT NULL,
    `serial` varchar(32) NOT NULL,
    `gcyo_id` varchar(32) NOT NULL,
    `purch_date` date NOT NULL,
    `purc_price` varchar(32) NOT NULL,
    `invoice_num` varchar(32) NOT NULL,
    `asset_image` varchar(256) NOT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1 COLLATE = latin1_swedish_ci;

INSERT INTO `asset_audit` (`audit_id`, `audit_asset_id`, `audit_date`, `audit_present`, `audit_repairs`, `audit_comment`) VALUES
(1, '1', '2023-03-01', '1', '0', 'this was the second audit of this piano, what a fine piano it is'),
(2, '1', '2023-02-01', '1', '0', 'this is the first audit date'),
(3, '1', '2023-04-04', '1', '0', 'these are the comments'),
(4, '1', '2023-04-04', '1', '1', 'repairs required'),
(5, '1', '2023-04-04', '0', '0', 'not in stock'),
(9, '3', '2023-04-02', '1', '0', 'this is the second asset');

INSERT INTO `asset` (`asset_id`, `type`, `supplier`, `asset_name`, `asset_desc`, `qty`, `serial`, `gcyo_id`, `purch_date`, `purc_price`, `invoice_num`, `asset_image`) VALUES
(1, '1', '1', 'Grand Piano', 'big piano on wheels', 1, '222333', '1001', '2023-04-03', '13,000.65', '1420TY445', 'images/1_Grand Piano.png'),
(3, '9', '3', 'Camera', 'avkans camera', 1, '22233333', '1004', '2023-03-28', '1,200.00', '1420TY44533', '');

这是我一直试图运行的查询的胆量。

$sql="SELECT * FROM asset 
  LEFT JOIN asset_type ON asset.type = asset_type.asset_type_id 
  LEFT JOIN suppliers ON asset.supplier = suppliers.supplier_id 
  LEFT JOIN asset_audit ON asset.asset_id = asset_audit.audit_asset_id 
  WHERE
      asset_audit.audit_id = (
          SELECT MAX(audit_id)
          FROM asset_audit
          WHERE asset.asset_id = asset_audit.audit_asset_id
          )
  ORDER BY asset.type ASC, asset.asset_name ASC";

然后，我要查询的代码填满了一个表格（此页面还有一堆其他代码，这是执行我遇到麻烦的这一部分的小节（这是来自phpize的片段，显示了单个结果）。它应该为列出的每个资产显示一行。一项资产未进行审计，因此审计日期为空;其他资产已进行多次审核，因此应显示上次审核日期。

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 31
    [lengths] => 
    [num_rows] => 2
    [type] => 0
)

这是生成的数组。它只包含一个响应，并且该响应的上次审核日期是正确的，但第二个资产的数组中没有响应。我尝试了第二个资产的审计条目，而第二个资产没有审计条目;它有同样的问题，它只带回第一个资产的数据。

Array
(
    [asset_id] => 1
    [type] => 1
    [supplier] => 1
    [asset_name] => Grand Piano
    [asset_desc] => big piano on wheels
    [qty] => 1
    [serial] => 222333
    [gcyo_id] => 1001
    [purch_date] => 2023-04-03
    [purc_price] => 13,000.65
    [invoice_num] => 1420TY445
    [asset_image] => images/1_Grand Piano.png
    [asset_type_id] => 1
    [asset_type] => Percussion
    [asset_sub_type] => Tuned Percussion
    [supplier_id] => 1
    [supplier_name] => Piano Supplier 
    [supplier_add_line_1] => line 1
    [supplier_add_line_2] => line 2
    [supplier_suburb] => suburb
    [supplier_postcode] => 2321
    [supplier_state] => state
    [supplier_phone] => 2034956857
    [supplier_contact_name] => Peter supplier
    [supplier_notes] => this is /  some %notes# about"" the  'man
    [audit_id] => 8
    [audit_asset_id] => 1
    [audit_date] => 2023-04-04
    [audit_present] => 1
    [audit_repairs] => 0
    [audit_comment] => this was the second audit of this piano, what a fine piano it is
)