mysql - 从该查询中的另一个表中获取值

Question

此查询运行良好，但问题是我需要获取另一个名为 rooms 的表中的房间号。下面的查询从只有 room_id 的 reservations 表中获取数据:

$select_table = "SELECT id, name, phone, email, address, country, adults, childs, purpose, booking_type, remarks, checkin, checkout, room_id, status, paid, date FROM reservations WHERE DATE_FORMAT(date, '%Y-%m') = '$date' ORDER BY id DESC";
$stmt = $db->prepare($select_table);
$stmt->execute();
$rows = $stmt->fetch(PDO::FETCH_ASSOC);
if ($rows)
{
getcsv(array_keys($rows));
}
while($rows)
{
getcsv($rows);
$rows = $stmt->fetch(PDO::FETCH_ASSOC);
}

我到底如何从 rooms 表中获取上述查询中的 number 列？

Answer 1

加入房间表!

SELECT r.id, r.name, r.phone, r.email, r.address, r.country, r.adults, r.childs, r.purpose,
       r.booking_type, r.remarks, r.checkin, r.checkout, r.room_id, r.status, r.paid, r.date,
       rooms.number  
FROM reservations r
JOIN rooms ON r.room_id = rooms.id
WHERE DATE_FORMAT(r.date, '%Y-%m') = '$date'
ORDER BY r.id DESC

(我厌倦了一遍又一遍地输入 reservations，因此我为该表创建了一个表别名 r。)