query giving error

[medioman]

I have the code below and it is giving me an error



SELECT hp.*
FROM hplcslot_dc AS hp INNER JOIN Booking ON hp.HPLC = Booking.HPLC
WHERE (((hp.id) Not In (SELECT hplcslot_dc.id
FROM Booking INNER JOIN hplcslot_dc ON Booking.HPLC = hplcslot_dc.HPLC
where (Booking.startdate <= hplcslot_dc.start AND Booking.enddate >= hplcslot_dc.start) OR
(Booking.startdate <= hplcslot_dc.end AND booking.enddate >= hplcslot_dc.end) OR
(Booking.startdate >= hplcslot_dc.start AND booking.enddate <= hplcslot_dc.end))));

any idea ?

 

[pr2-eugin]

To start, I don't think you can have 2 FROM statements and WHERE clause should be after the FROM.

Dale

Dale, that is not entirely TRUE.. As I can see that the OP has used SubQuery.. Which can have two FROM clause..

The other way I would go about is create the SubQuery as a separate query and then see if I get the desired result then use the Unmatched Query wizard to get the job done..

 

[rzw0wr]

pre-eugin, Thank you for correcting my post.
I am still trying to learn queries.

Thanks again.
Dale

PS, Post deleted. Miss leading information.

 

[pr2-eugin]

pre-eugin, Thank you for correcting my post.
I am still trying to learn queries.

We learn something new everyday Dale..

 

[SOS]

I have the code below and it is giving me an error

Posting that without giving the exact error is not very helpful for troubleshooting. It can speed the process if you provide the error too.

 

[medioman]

table booking

----------------------------------------------------------------------------------------------------------------------------
| ID | HPLC | StartDate | Testing | EndDate |
----------------------------------------------------------------------------------------------------------------------------
| 1 | HPLC1 | 08/06/2013 12:00:00 | Mirta RS | 08/06/2013 18:00:00 |
----------------------------------------------------------------------------------------------------------------------------
| 2 | HPLC2 | 09/06/2013 10:00:00 | Olan ASS | 09/06/2013 15:00:00 |
----------------------------------------------------------------------------------------------------------------------------
| 3 | HPLC1 | 08/06/2013 10:00:00 | Mirta ASS | 08/06/2013 12:00:00 |
----------------------------------------------------------------------------------------------------------------------------
| 4 | HPLC3 | 08/06/2013 09:00:00 | RISP DISS | 08/06/2013 16:00:00 |
----------------------------------------------------------------------------------------------------------------------------
table hplc
-----------------------------------------
| ID | HPLC |
-----------------------------------------
| 1 | HPLC1 |
-----------------------------------------
| 2 | HPLC2 |
-----------------------------------------
| 3 | HPLC3 |
-----------------------------------------
| 4 | HPLC4 |
-----------------------------------------
| 5 | HPLC5 |
-----------------------------------------
table hplcslot.... I only pasted a piece of it just to have an idea as it has 480 rows.
"HPLC2",1," 06:00:00"," 06:15:00"
"HPLC2",2," 06:15:00"," 06:30:00"
"HPLC2",3," 06:30:00"," 06:45:00"
"HPLC2",4," 06:45:00"," 07:00:00"
"HPLC2",5," 07:00:00"," 07:15:00"
"HPLC2",6," 07:15:00"," 07:30:00"
"HPLC2",7," 07:30:00"," 07:45:00"
"HPLC2",8," 07:45:00"," 08:00:00"
"HPLC2",9," 08:00:00"," 08:15:00"
"HPLC2",10," 08:15:00"," 08:30:00"
"HPLC2",11," 08:30:00"," 08:45:00"
"HPLC2",12," 08:45:00"," 09:00:00"
"HPLC2",13," 09:00:00"," 09:15:00"
"HPLC2",14," 09:15:00"," 09:30:00"
"HPLC2",15," 09:30:00"," 09:45:00"
"HPLC2",16," 09:45:00"," 10:00:00"
"HPLC2",17," 10:00:00"," 10:15:00"
"HPLC2",18," 10:15:00"," 10:30:00"
"HPLC2",19," 10:30:00"," 10:45:00"
"HPLC2",20," 10:45:00"," 11:00:00"
"HPLC2",21," 11:00:00"," 11:15:00"


-------------------------------------------------



basically what I want to do is run a query which will list all the slots available [from 6 am to 6 am the next day with 15min slots] (hplcslot) (ie. which are not booked in the table booking) for all the hplc's listed in the table HPLC.

this would be similar to have multiple rooms with multiple bookings per day, to list the available rooms.. not the rooms booked.

I tried to above code but it doesn't seem that I have much success.