将 SQL 查询从一个条件更改为多个条件 [英] Changing SQL query from one condition to several

问题描述

我有一个 SQL 查询，它检查名为 ts_allocation 的表中的值，以查看是否已分配"匹配的房间(次要条件).如果它们被分配"，那么它不会在结果中返回它们.不幸的是，由于设计限制，我的表中不能再有这种情况了.

I have an SQL query which checks for values in a table called ts_allocation to see if the matching rooms that have been "Allocated" (a secondary condition). If they are "Allocated" then it does not return them in the result. Unfortunately due to design constraints, I can't have this condition in my table anymore.

我现在想要更改它，以便它查看房间是否具有待处理"、失败"或拒绝"以外的值.如果它是三个中的任何一个，那么结果将被返回并且不会被忽略(与以前不同).

I now want to change it so that it sees if the rooms have a value either than "Pending", "Failed" or "Declined". If it is any of the three then the result will be returned and not ignored (unlike before).

在第一个 where 子句中获得房间后，系统需要检查它们在 ts_allocation 表中是待定"、失败"还是拒绝".一个房间可以在某一天或某个时期被预订——但如果它的状态仍然是待定"、失败"或拒绝"，那么它就会被查看.其他任何情况都意味着房间已被预订.

Once the rooms have been acquired in the first where clause the system needs to check whether they are "Pending", "Failed" or "Declined" in the ts_allocation table. A room can be booked for a certain day or period - but if its status remains "Pending", "Failed" or "Declined" then it is viewed. Anything else would imply the room is booked.

我希望我在这里说得通...这是我目前的代码:

I hope I'm making sense here... here's my code so far:

SELECT 
  COUNT(*) totalCount 
FROM 
  ts_room rm
WHERE building_id=:building_id AND  
  NOT EXISTS (
    SELECT 1
    FROM ts_roompref rp
      JOIN ts_request rq
      ON rp.request_id = rq.id
      AND day_id = 4
      AND period_id = ".$i."
    WHERE 
      rm.id = rp.room_id)
AND NOT EXISTS (
    SELECT 1
    FROM ts_roompref rp
      JOIN ts_allocation a
      ON rp.request_id = a.request_id
      AND a.status = 'Allocated'
    WHERE 
      rm.id = rp.room_id)

推荐答案

我假设您希望返回房间钥匙作为查询结果...

I'm assuming you want the room key back as the result of the query...

SELECT DISTINCT rm.Id as RoomId
FROM ts_room rm LEFT JOIN ts_roompref rp ON rp.room_id = rm.id
  LEFT JOIN ts_request rq ON rq.id = rp.request_id
  LEFT JOIN ts_allocation a ON a.request_id = rq.id
WHERE building_id = :building_id
  AND (a.status IS NULL OR a.status IN ('Pending', 'Failed', 'Declined'))

这篇关于将 SQL 查询从一个条件更改为多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！