sql - 左外连接查询

Question

我有两张表，一张用于预订，一张用于房间分配。 Booking 表有一个 id，room allocations 表有一个 id 和 room_id(id 指的是预订 ID)。我想查找在房间分配表中没有条目的所有预订的列表。

我做了以下查询:

$sql = "SELECT booking.id, booking.username, booking.contact_name 
FROM booking LEFT OUTER JOIN rbs_room_allocation 
USING id 
WHERE (rbs_room_allocation.id IS NULL) 
AND (booking.trip_id = :trip_id) 
AND (booking.paid = booking.total)";

$params = array('trip_id' => $trip_id);
$result = $dbh->getAll($sql, null, $params);

但是我收到一条错误消息:

[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id WHERE (rbs_room_allocation.id IS NULL) AND (booking.trip_id = ?) AND (booking' at line 1]

我不知道为什么这不起作用，我尝试将 id 放入查询而不是使用参数，但我得到了同样的错误，所以我假设它与我的查询有关。非常感谢任何帮助!

Answer 1

试试这个不存在查询:

$sql = "SELECT b.id, b.username, b.contact_name 
FROM booking b
WHERE 
b.trip_id = :trip_id
and b.paid = b.total
AND NOT EXISTS (select 1 from rbs_room_allocation where id = b.id)";