| microtom 2006-11-10, 5:15 am |
| Hello helpers
I have two tables and I'd like to recieve all results for computers
that have not been reserved for a specified time period (eg. 11/27/2006
to 11/29/2006). Here's a small sample of how my database looks like:
-----Table "Machines"
-MachineID (int)
-MachineName (nvarchar)
-----Table "Reservations"
-ReservationID (int)
-MachineID (int)
-DateFrom (DateTime)
-DateTo (DateTime)
Machines content:
1, COMPUTER1
2, COMPUTER2
3, COMPUTER3
Reservations content:
1, 1, 11/10/2006, 11/20/2006
1, 2, 11/18/2006, 11/21/2006
Anyone got an idea on how to recieve this result with a single query?
I tried tthings like:
SELECT Machines.*, Reservations.*
FROM Machines
LEFT JOIN Reservations ON Machines.MachineID = Reservations.MachineID
WHERE
Reservations.ID != null
OR
(
(Reservations.DateFrom < 'myfromdate' AND Reservations.DateTo >
'mytodate')
) ..and so on
I found an article which pretty much does the trick using SQL views,
but I'm trying to achieve this using a .net web aplication. Here's the
link: http://support.microsoft.com/kb/245074
Any thoughts are highly appreciated!
Tom
|