Solved

Query selectint item when it should not

Posted on 2010-11-23
19
259 Views
Last Modified: 2012-05-10
What i am trying to do is select a vehcile from tblGSAVehicles based on its location, vehcile type and that it is not in use in tblSchedule.  The problem is that I can limit it to one vehicle but it always pick it no matter if it is scheduled for those dates .  How can I limit it to only if its not scheduled for a start date or end date in tblschedule.  The code below I thought would work.


So with code below it pulls up the same vehicle 3 times I put in a distinct and this goes away, but at this point I am  confused and to why the vehicle is being pulled when its scheduled in tblSchedue and shoould pull nothing.
SELECT	 v.intVehicleID, 
		t.strVehicleType, 
		v.intYear, 
		v.strMake, 
		v.strModel, 
		v.strColor, 
		v.intPass 
FROM	tblGSAVehicles AS v LEFT JOIN 
		tblSchedule AS s ON v.intVehicleID = s.intVehicleID INNER JOIN 
		tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID 
WHERE	v.intLocID = 1 AND 
		t.intVehicleTypeID = 4 AND 
		v.bitInUse = 1 AND NOT EXISTS (Select * from tblschedule where
		((Convert(varchar(10), s.dtStartDate, 101) NOT BETWEEN '11/23/2010' AND '11/25/2010') OR s.dtStartDate IS NULL) AND 
		((Convert(varchar(10), s.dtStartDate, 101) NOT BETWEEN '11/23/2010' AND '11/25/2010') OR s.dtEndDate IS NULL))

Open in new window

0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +1
19 Comments
 
LVL 12

Expert Comment

by:ill
ID: 34197629
-- you mean like removing LEFT from LEFT JOIN on line 8 ?
SELECT       v.intVehicleID,
            t.strVehicleType,
            v.intYear,
            v.strMake,
            v.strModel,
            v.strColor,
            v.intPass
FROM      tblGSAVehicles AS v JOIN
            tblSchedule AS s ON v.intVehicleID = s.intVehicleID INNER JOIN
            tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID
WHERE      v.intLocID = 1 AND
            t.intVehicleTypeID = 4 AND
            v.bitInUse = 1 AND NOT EXISTS (Select * from tblschedule where
            ((Convert(varchar(10), s.dtStartDate, 101) NOT BETWEEN '11/23/2010' AND '11/25/2010') OR s.dtStartDate IS NULL) AND
            ((Convert(varchar(10), s.dtStartDate, 101) NOT BETWEEN '11/23/2010' AND '11/25/2010') OR s.dtEndDate IS NULL))
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34197635
try

FROM      tblGSAVehicles AS v LEFT JOIN
-->
FROM      tblGSAVehicles AS v JOIN
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34197638
please evaluate this:
SELECT   v.intVehicleID, 
                t.strVehicleType, 
                v.intYear, 
                v.strMake, 
                v.strModel, 
                v.strColor, 
                v.intPass 
FROM    tblGSAVehicles AS v 
LEFT JOIN  tblSchedule AS s 
  ON v.intVehicleID = s.intVehicleID 
 AND (s.dtStartDate IS NULL OR  s.dtStartDate < CONVERT(datetime, '11/25/2010', 101) + 1)
 AND (s.dtEndDateIS NULL OR s.dtEndDate >= CONVERT(datetime, '11/25/2010', 101) )

INNER JOIN  tblVehicleType AS t 
  ON v.intVehicleTypeID = t.intVehicleTypeID 
WHERE v.intLocID = 1 
  AND t.intVehicleTypeID = 4 
  AND v.bitInUse = 1 
  AND s.intVehiculeID IS NULL

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 125 total points
ID: 34197693
try this
SELECT	 v.intVehicleID, 
		t.strVehicleType, 
		v.intYear, 
		v.strMake, 
		v.strModel, 
		v.strColor, 
		v.intPass 
FROM	tblGSAVehicles AS v INNER JOIN 
		tblSchedule AS s ON v.intVehicleID = s.intVehicleID INNER JOIN 
		tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID 
WHERE	v.intLocID = 1 AND 
		t.intVehicleTypeID = 4 AND 
		v.bitInUse = 1 AND 
		(s.dtStartDate IS NULL OR s.dtStartDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101)) 
		AND 
		(s.dtEndDate IS NULL OR s.dtEndDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101))

Open in new window

0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34197722
formatted
SELECT v.intVehicleID, t.strVehicleType, v.intYear, v.strMake, v.strModel, v.strColor, v.intPass 
  FROM tblGSAVehicles AS v INNER JOIN 
       tblSchedule AS s ON v.intVehicleID = s.intVehicleID INNER JOIN 
       tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID 
 WHERE v.intLocID = 1 AND 
       v.bitInUse = 1 AND 
       t.intVehicleTypeID = 4 AND 
       (s.dtStartDate IS NULL OR s.dtStartDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101)) 
       AND 
       (s.dtEndDate IS NULL OR s.dtEndDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101))

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 34198252
angelIII:
This gives me 4 results, which would not be correct becuase it should be limiting by intLocId and intVehicleTypeId as well as if it si scheduled in tblSchedule.  It comes up with this.  4 is scheduled for those dates and the rest should not show since they are not the correct vehicleId.

VechicleId    type              year                 make            modle           color            pass
4      Van      2002      Chevrolet      CG3300      White      15
5      Van      2009      Chevrolet      CG3300      White      15
8      Van      2008      Chevrolet      CG3300      Tan      15
9      Van      2008      Chevrolet      CG3300      Black      15
0
 

Author Comment

by:kdeutsch
ID: 34198272
ill:,
Still selct wrong it produces this result.  Which is 3 of the same vehicle but nothing should appear as this vechile is schedules in tblSchedule.  Otherwise its limiting to correct vehicle.


4      Van      2002      Chevrolet      CG3300      White      15
4      Van      2002      Chevrolet      CG3300      White      15
4      Van      2002      Chevrolet      CG3300      White      15
0
 

Author Comment

by:kdeutsch
ID: 34198292
HainKurt,
Ist query produces same resulst but 2 of ame vehicle.  this same vehicle is scheduled already with those dates in tblSchedule so it should not show up.

4      Van      2002      Chevrolet      CG3300      White      15
4      Van      2002      Chevrolet      CG3300      White      15
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34198368
try this
SELECT v.intVehicleID, t.strVehicleType, v.intYear, v.strMake, v.strModel, v.strColor, v.intPass 
  FROM tblGSAVehicles AS v INNER JOIN 
       tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID 
 WHERE v.intLocID = 1 AND 
       v.bitInUse = 1 AND 
       t.intVehicleTypeID = 4 AND 
       v.intVehicleID not in (
         select s.intVehicleID 
           FROM tblSchedule AS s
           WHERE (s.dtStartDate IS NULL OR s.dtStartDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101)) 
                 AND 
                 (s.dtEndDate IS NULL OR s.dtEndDate NOT BETWEEN convert(datetime, '11/23/2010', 101) AND convert(datetime, '11/25/2010', 101))
       )

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 34198379
I think I had a wrong parameter:
SELECT   v.intVehicleID, 
                t.strVehicleType, 
                v.intYear, 
                v.strMake, 
                v.strModel, 
                v.strColor, 
                v.intPass 
FROM    tblGSAVehicles AS v 
LEFT JOIN  tblSchedule AS s 
  ON v.intVehicleID = s.intVehicleID 
 AND (s.dtStartDate IS NULL OR  s.dtStartDate < CONVERT(datetime, '11/25/2010', 101) + 1)
 AND (s.dtEndDateIS NULL OR s.dtEndDate >= CONVERT(datetime, '11/23/2010', 101) )

INNER JOIN  tblVehicleType AS t 
  ON v.intVehicleTypeID = t.intVehicleTypeID 
WHERE v.intLocID = 1 
  AND t.intVehicleTypeID = 4 
  AND v.bitInUse = 1 
  AND s.intVehiculeID IS NULL

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 34198403
I am uploading the tblSructure for 3 tables.  basically what I am trying to do is get a vechile base4d on location and vehciletype that is not scheduled in tblschedule.
TblSctructure.xlsx
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34198408
AngelIII,

tblGSAVehicles AS v LEFT JOIN  tblSchedule AS s

does not eliminate any vehicle from resultset if tehre is a schedule for that car... so all queries in this form will give wrong result

...
FROM    tblGSAVehicles AS v LEFT JOIN  tblSchedule AS s ON v.intVehicleID = s.intVehicleID ...
0
 

Author Comment

by:kdeutsch
ID: 34198540
All,
Actuallu both queries are returning the same exact result.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34198735
HainKurt:
  you overlook the condition in the WHERE clause ... WHERE s.xxxx IS NULL  ...
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34198898
yes I missed that part AngelIII ;) but what happened to interval?

AND (s.dtStartDate IS NULL OR  s.dtStartDate < CONVERT(datetime, '11/25/2010', 101) + 1)

-->

AND (s.dtStartDate IS NULL OR  (s.dtStartDate >= CONVERT(datetime, '11/23/2010', 101) and s.dtStartDate < CONVERT(datetime, '11/25/2010', 101) + 1)

query should eliminate the schedules between 23 & 25 also should eliminate the null values...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34199206
here are my conditions:
 AND (s.dtStartDate IS NULL OR  s.dtStartDate < CONVERT(datetime, '11/25/2010', 101) + 1)
 AND (s.dtEndDateIS NULL OR s.dtEndDate >= CONVERT(datetime, '11/23/2010', 101) )

Open in new window


so, this will match IF:
* there is a schedule that either starts before the "end date" of the schedule interval, or has no start date at all
AND IF
* there is a schedule that either ends after the "start date" of the schedule interval, or has no end date at all

so, if you find a schedule with start+end date both NULL, that will match for the JOIN
so, if you find a schedule with start date <= 25/11 (or start date null) and end date >= 23/11 (or NULL), that will also match for the JOIN
if you have a schedule with start date > 25/11 (with hence end date > 25/11 also) OR end_date < 23/11 (and hence start date < 23/11 also) , it will not match the JOIN

the additional condition WHERE s.<somefield> IS NULL will make that if the (LEFT) JOIN had a match, the actual data row will NOT be returned.
so, if the LEFT JOIN had no match, the actual row WILL by returned

I have worked with this kind of conditions quite some time, I am confident it shall work...
if the understanding of the requests is correct
0
 

Author Comment

by:kdeutsch
ID: 34200037
All,
We have done further testing and here is what is happening with both sets.

hainKurt,
We can schedule but for some reason originally we thought code was working but it keeps coming up with the same 3 vehicles 5, 8, 9 and we thought it should show 4 as well but it does not.  But is we schedule the other vehicles they still come up.  as we schedule them they still come up as not being scheduled.

angelIII:
I every situation that we have run the vehicle scheduler the code has worked to cut out the vehicle if it is scheduled in tblSchedule.  Meeting all the requirements of location and type of vehicle.

We are still doing more extensive testing to make sure every situation passes.
0
 

Author Comment

by:kdeutsch
ID: 34206883
All,

Ok we have gotten both statements to work in all sistuation for
HainKurt solution weused the same conditions statement as angellls and it works just fine.
Tahnsk for the help.
0
 

Author Closing Comment

by:kdeutsch
ID: 34206904
Thanks for the help.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question