[ratpois0n]

Hello, I am currently in a pickle with my database I am creating.

I currently have two tables, where one holds information about vehicles and another table that has entries on those vehicles being hired out to people. On the second table I have a field called "Finished" which determines whether the vehicle is back for use. The idea is that you cannot hire out the same vehicle and create a new entry while the previous entry of that same vehicle is still hired out.

I want to be able to create a query where it lists all the vehicles that do not have an entry in the Hiring table where one of the entries has "Finished" left False. (It is a Yes/No). That, I have no problem with, but I also want it to list the vehicles that do not have an entry at all in the second table in the event where I add a new vehicle in and it hasn't been hired out before. I am using this query to display all the available vehicles for when I create a Hire Out form.

Here is the SQL code I pulled from Access. It currently only displays the Available vehicles which do have an entry in the second table.

SELECT tblVehicles.RegoID, tblVehicles.Type, tblVehicles.Price, qryFinishedHire.RegoID, qryFinishedHire.Finished
FROM tblVehicles LEFT JOIN qryFinishedHire ON tblVehicles.[RegoID] = qryFinishedHire.[RegoID]
WHERE (((qryFinishedHire.Finished)=True));

I currently cannot find any way to have it also display the available vehicles which do not have an entry in the second table. Is this a matter of having to learn SQL code and doing it manually?

Thanks in advance.

[modi123_1]

With SQL you should just slow down and write out what you want.. it may be a bit verbose (and you can totally refine it later), but just thing of nice big blocks of ice you are carving chunks off of to get what you want.

The gist here is to work with getting all the vehicles that are: not out (aka have no return date), to ignore entries in the rental of those cars checked out (so not to confuse things), and that have a car out that is not between our given dates!

vehicles

Quote

ID TYPE PRICE
----------- ---------- -----------
1 car 5
2 truck 56
3 van 30
4 trike 2

rental info

Quote

ID VEHICLE_ID DAY_OUT DAY_IN
----------- ----------- ----------------------- -----------------------
1 1 2013-01-01 00:00:00.000 2013-01-02 00:00:00.000
2 1 2013-05-01 00:00:00.000 NULL
3 2 2013-04-01 00:00:00.000 2013-04-25 00:00:00.000
4 2 2013-04-30 00:00:00.000 NULL
5 4 2013-04-30 00:00:00.000 2013-05-01 00:00:00.000

Output for a vehicle available between '06/01/2013' and '06/03/2013':

Quote

ID TYPE PRICE
----------- ---------- -----------
3 van 30
4 trike 2

-- car table
create table #vehicle(ID INT, [TYPE] VARCHAR(10), PRICE INT)

-- car data
insert into #vehicle(ID, [TYPE], PRICE)
values(1, 'car', 5)

insert into #vehicle(ID, [TYPE], PRICE)
values(2, 'truck', 56)

insert into #vehicle(ID, [TYPE], PRICE)
values(3, 'van', 30)

insert into #vehicle(ID, [TYPE], PRICE)
values(4, 'trike', 2)

-- showing our data
select *
from #vehicle

-- when cars are rented
create table #RENTAL(ID INT, VEHICLE_ID INT, DAY_OUT datetime, DAY_IN datetime)

-- rental data
insert into #RENTAL(ID, VEHICLE_ID, DAY_OUT, DAY_IN)
values(1, 1, '01/01/2013', '01/02/2013')

insert into #RENTAL(ID, VEHICLE_ID, DAY_OUT, DAY_IN)
values(2, 1, '05/01/2013', null)

insert into #RENTAL(ID, VEHICLE_ID, DAY_OUT, DAY_IN)
values(3, 2, '04/01/2013', '04/25/2013')

insert into #RENTAL(ID, VEHICLE_ID, DAY_OUT, DAY_IN)
values(4, 2, '04/30/2013', null)

insert into #RENTAL(ID, VEHICLE_ID, DAY_OUT, DAY_IN)
values(5, 4, '04/30/2013', '05/01/2013')

-- showing rental data
select *
from #RENTAL

-- date range of when we want to rent a vehicle.
declare @startdt DATETIME
declare @enddt DATETIME

set @startdt = '06/01/2013'
set @enddt = '06/03/2013'

-- take this real slow and just spell out what you want.

select b.ID, b.TYPE, b.PRICE -- we want the available vehicle data
from #RENTAL a -- from our rental table.
join #vehicle b on a.VEHICLE_ID = b.ID  -- joined on the vehicle table.
where a.ID not in (select id from #RENTAL where DAY_IN is null) -- vehicles that are not in use (aka not checked in)
and a.VEHICLE_ID not in (select VEHICLE_ID from #RENTAL where DAY_IN is null) -- to ignore other rows that have vehicles checked out
and not (a.DAY_OUT between @startdt and @enddt) -- and the day out is not between when we want to rent the vehicle

union -- since unions typically find what data matches between tables, to collect vehicles that do not have a rental entry yet

select ID, TYPE, PRICE -- grab the vehicle data
from #vehicle  -- from vehicles
where ID not in (select  VEHICLE_ID from #RENTAL)  -- that do not have an entry in the rental table.

drop table #RENTAL
drop table #vehicle

[ratpois0n]

Thanks a lot! Makes sense now.

I got it working, this is the code I came up with from what I learnt from you.

SELECT *
FROM tblvehicles AS T1
WHERE (((T1.regoid) Not In (select top 1 regoid from tblhiring where finished = False))) OR (((T1.regoid) Not In (select regoid from tblhiring)));

[rgfirefly24]

I would like to point out that if you are using inner queries like that you can usually get away with a join statement.

SELECT *
FROM tblvehicles AS T1
LEFT JOIN tblhiring T2
    ON T1.regoid = T2.regoid 
WHERE ISNULL(T2.finished,False) = False

You could also change the where clause to be like so:

WHERE T2.Finished IS NULL OR T2.Finished = False

Both of those should produce the same results you want with cleaner code that will preform far better

[e_i_pi]

I agree with rgfirefly24 here. You use of the NOT IN clause, while easy to read, is painful for the engine to process, as it needs to generate the entire search set, and then test to see if the value is in that set or not. Replacing the NOT IN with a clause like rgfirefly24 has given is far more optimal in terms of query performance.

[BobRodes]

I have heard the same, and it's probably true in a lot of cases. However, this discussion suggests that it isn't always the case.