php - 比较两列之间的日期时 LEFT JOIN 不起作用

Question

这里我正在做出租车分配模块，在这里我将解释我的要求，管理员按轮类方式将出租车分配给员工。这些细节我存储在 cab_allocation 分配表中。

cab_allocation (Table Name)

allocationId        from_allocationDate       to_allocationdate       cabId    shiftTiming

1                   2017-09-08                 2017-09-30             CBX100     1
2                   2017-09-08                 2017-09-30             CBX100     2

在此之后我想跟踪这次旅行是否每天都在发生，因为我将此跟踪详细信息存储在 trip_details 表中

trip_details (table name)

id      allocationId   tripDate   startTime   endTime      tripStatus

 1             1       2017-09-08  09:30:00    11:30:00       1
 2             2       2017-09-08  12.10.00    02:30:00       1
 3             1       2017-09-09  12.10.00                   0

昨天(2017-09-08) allocationId 1 & 2 行程已经完成(tripStatus 1 表示完成), 今天(2017-09-09) allocationId 1 行程已经开始但是还没有完成(tripStatus 0 表示没有完成,就像正在进行的旅行)和今天(2017-09-09)allocationId 2 旅行它似乎没有开始所以没有在 trip_details 表中的条目。

Now what i want means today howmany allocation is there i need that details from cab_allocation table based cabId, Suppose today that trip is completed means trip details should not display, Suppose today trip is not completed means i want display the details.

**我试过这样**

SELECT a.allocationId, a.shiftTiming, a.cabId 
FROM cab_allocation a 
LEFT JOIN trip_details b ON a.allocationId = b.allocationId 
WHERE a.cabId='CBX100' AND b.tripStatus != '1' AND a.from_allocationDate >= '2017-09-09' AND a.to_allocationdate <= '2017-09-09'

但是我没有找到任何结果，但是根据我的表结构我需要这样的输出

    {
"status": "success",
"count": 2,
"data": [
    {
        "allocationId": "1",
        "shiftTiming": "1",
    },
    {
        "allocationId": "2",
        "shiftTiming": "2",
    }
]
}

My Updated Code

SELECT a.allocationId, a.date, a.shiftTiming, a.cabId 
FROM cab_allocation a 
LEFT JOIN trip_details b ON a.allocationId = b.allocationId 
WHERE a.cabId='CBX100' AND b.tripStatus != '1' AND a.from_allocationDate <= '2017-09-09' AND
  a.to_allocationdate >= '2017-09-09'

Answer 1

首先，您不需要为此使用左连接。使用内部联接。您的意图是进行过滤。

其次，您的 where 子句是:

WHERE a.cabId='CBX100' AND b.tripStatus <> '1' AND
      a.from_allocationDate >= '2017-09-09' AND
      a.to_allocationdate <= '2017-09-09'

你有倒退日期的条件。你想要:

WHERE a.cabId='CBX100' AND b.tripStatus <> '1' AND
      a.from_allocationDate <= '2017-09-09' AND
      a.to_allocationdate >= '2017-09-09'

开始日期需要在结束日期之前。