从MySQL联接中的第一个表中获取所有结果

Question

 SELECT R.name room_name,
        R.id room_id,
        UD.discount 
   FROM user_discounts UD 
   LEFT 
   JOIN rooms R 
     ON R.id = UD.room_id
  WHERE UD.user_id = 1482
    AND UD.hotel_id = 2

Answer 1

如果您想要所有房间，请反转表关系

SELECT `R`.`name` as `room_name`, `R`.`id` as `room_id`, `UD`.`discount` as `discount`
FROM  `rooms` as `R`
LEFT JOIN `user_discounts` as `UD` ON `R`.`id`= `UD`.`room_id`
             AND`UD`.`user_id` = '1482'
             AND `UD`.`hotel_id` = '2'

但是还需要修改where子句。它们可以代替原来的where子句作为联接条件的一部分。

where子句的影响很容易被忽略，但是如果在where子句中引用左联接表，则还必须允许该表中的数据为NULL。例如

SELECT `R`.`name` as `room_name`, `R`.`id` as `room_id`, `UD`.`discount` as `discount`
FROM  `rooms` as `R`
LEFT JOIN `user_discounts` as `UD` ON `R`.`id`= `UD`.`room_id`
WHERE (`UD`.`user_id` = '1482'
   AND `UD`.`hotel_id` = '2'
      )
   OR `UD`.`room_id` IS NULL

SELECT `R`.`name` as `room_name`, `R`.`id` as `room_id`, `UD`.`discount` as `discount`
FROM  `rooms` as `R`
LEFT JOIN `user_discounts` as `UD` ON `R`.`id`= `UD`.`room_id`
             AND`UD`.`user_id` = '1482'
             AND `UD`.`hotel_id` = `R`.`hotel_id`
WHERE `R`.`hotel_id` = 2