从MySQL联接的第一个表中获取所有结果 [英] Get all results from first table in a MySQL join

问题描述

我有两个桌子.我需要从第一张表中选择所有行(仅一个条件hotel_id = 2)，并从第二张表中选择所有行(基于条件).但是我使用的是左联接，只能从第二张表中获取数据.

I have two tables. I need to select all the rows (only one condition hotel_id=2) from first table and selected rows (based on condition) from 2nd table. But I'm using left join only get the datas from the 2nd table.

查询

 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

我需要显示所有房间，但现在在两个表中显示公共房间.

I need to show all rooms, but now shows the common in two tables.

推荐答案

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

Reverse the table relationships if you want all rooms

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子句，可以将它们用作连接条件的一部分.

But you will also need to alter the where clause as well. Instead of your original where clause they can be used as part of the conditions of the join instead.

可以很容易地忽略where子句的影响，但是如果您在where子句中引用左连接表，则还必须允许该表中的数据为NULL.例如

The effect of the where clause can easily be overlooked, but if you reference a left joined table in the where clause you must also allow for the data from that table to be NULL. e.g

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

---

如果饭厅有hotel_id，则:

---

If table rooms has a hotel_id then:

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

这篇关于从MySQL联接的第一个表中获取所有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！