首页 文章

根据我的条件，查询没有给出正确的结果

提问于
浏览 次
1

I have tables which name is given below

1）tblCarName
2）tblVehicle
3）tblModel
4）tblVehicleAvailability

在上面的三张 table 上，我有取得的结果，车辆未被预订，具体型号，然后是特定日期车辆可用或不可用的另一个状态车辆可用状态 .
但我得到的结果是哪辆车不可用 . （表示那些在tblVehicleAvailability表中的车）但我想要那些不在tblVehicleAvailability表中的车）

My Query

select 
    A.id,
    C.vsCarName+' ('+ A.EngineNum+' )' as vsCarName   
from tblCarName C 
join tblVehicle A on C.id = A.CarId
inner join tblModel B on A.ModelId = B.id 

--join tblVehicleAvailability VA on  VA.CarId = A.id

WHERE A.id not in 
(
    SELECT 
        VehicleId 
    FROM 
        tblBooking
    WHERE 
        A.ActiveInactive = 1
    AND 
        Convert(datetime,'2013-09-19 00:00:00.000') --start date
            BETWEEN FromDateWithStartTime AND ToDateWithEndTime
    OR 
        Convert(datetime,'2013-09-20 00:00:00.000')  --endDate
            BETWEEN FromDateWithStartTime AND ToDateWithEndTime
    OR 
        FromDateWithStartTime 
            BETWEEN Convert(datetime,'2013-09-19 00:00:00.000') --startdate  
          AND Convert(datetime,'2013-09-20 00:00:00.000')  --enddate  
    OR 
        ToDateWithEndTime 
            BETWEEN Convert(datetime,'2013-09-19 00:00:00.000')--start date  
            AND Convert(datetime,'2013-09-20 00:00:00.000')--enddate
) 
AND  
    A.ModelId='3' 
AND   
    Convert(datetime,'2013-09-19 00:00:00.000')  --start date  
    BETWEEN VA.leaveDate AND VA.leaveToDate  
OR 
    Convert(datetime,'2013-09-20 00:00:00.000') --endDate  
    BETWEEN VA.leaveDate AND VA.leaveToDate  
OR 
    VA.leaveDate 
    BETWEEN Convert(datetime,'2013-09-19 00:00:00.000') --startdate  
    AND Convert(datetime,'2013-09-20 00:00:00.000')  --enddate  
OR 
    VA.leaveToDate
    BETWEEN Convert(datetime,'2013-09-19 00:00:00.000') --start date
    AND Convert(datetime,'2013-09-20 00:00:00.000')
sql

2 回答

  • 0

    试试这个：

    select A.id ,C.vsCarName+' ('+ A.EngineNum+' )' as vsCarName   
from tblCarName C 
inner join tblVehicle A on C.id=A.CarId
inner join tblModel B on A.ModelId=B.id 
where not exists (
    select null from tblBooking
    where A.id = VehicleId and
    A.ActiveInactive = 1 and 
    '20130919' <= ToDateWithEndTime 
    and '20130920' >= FromDateWithStartTime
    ) 
and  A.ModelId='3' AND
'20130919' <= VA.leaveToDate  
and '20130920' >= VA.leaveDate
    回复于
  • 1

    通常当我看到“OR”语句时，我开始添加（），这样我得到了我想要的东西 .

    编辑：但您可能还想看看使用LEFT JOIN tblVehicleAvailability

    和另外的where子句：

    WHERE CarID为NULL

    最好的问候，亨里克

    回复于

相关问题