Best select to get unreserved facilities

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best select to get unreserved facilities

Author

Topic

raymyster
Starting Member

10 Posts

Posted - 2009-10-20 : 05:12:14

Hello Community,

I will make this short as much as i can:

i have these 2 tables:

First table: (facility)

facility_id (auto-inc) facility_description (varchar 40)
---------------------- ---------------------------------
F1 usa_facility
F2 germany_facility
F3 France_facility

second table: (order)

order_id(integer) item_id(varchar 20) from_date(date) to_date (date)
----------------- ------------------- --------------- --------------
3 F1 15/10/2009 18/10/2009
5 F1 18/10/2009 19/10/2009
6 F3 01/01/2009 25/10/2009
7 F2 08/10/2009 26/10/2009

i applied the following code to get all he facilities that are currently not booked but it doesn't run:

 SELECT facility.facility_id
   FROM FACILITY LEFT JOIN [ORDER] ON  facility.facility_id = Clng([order].item_id)
WHERE (((Clng([order].item_id)) Is Null));

The result should be today 20/10/2009:
F1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-20 : 05:26:35

quote:
to get all he facilities that are currently not booked

why do you consider F2 and F3 not book for 20/10/2009 ? Isn't there order_id 6 and 7 which booked F3 from 01/01 to 25/10 and F2 from 08/10 to 26/10 ?

KH
[spoiler]Time is always against us[/spoiler]

raymyster
Starting Member

10 Posts

Posted - 2009-10-20 : 05:34:35

quote:
why do you consider F2 and F3 not book for 20/10/2009

I am sorry F1 is the not booked facility because when they booked it before now it expired. i edited the post back to correct, the query result should be F1.

Thank You so much for correcting me!, can you help with the query?

Share your knowledge it's one way to achieve immortality http://www.rabihtawil.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-20 : 05:41:04

[code]
SELECT facility.facility_id
FROM FACILITY LEFT JOIN [order] ON facility.facility_id = [order].item_id
AND [order].from_date >= dateadd(day, datediff(day, 0, getdate()), 0)
AND [order].to_date <= dateadd(day, datediff(day, 0, getdate()), 0)
WHERE [order].item_id is null
[/code]

KH
[spoiler]Time is always against us[/spoiler]

raymyster
Starting Member

10 Posts

Posted - 2009-10-20 : 06:20:31

amazingly that this query runs normaly in SQL server but does not run in data access

SELECT facility_id, facility_description
   FROM FACILITY LEFT JOIN [order] ON  facility.facility_id = [order].item_id  
                                   AND [order].to_date     >= GETDATE()
WHERE [order].item_id is null

error: JOIN expression not supported

That is weird

any idea of what is the alternative

Share your knowledge it's one way to achieve immortality http://www.rabihtawil.com

raymyster
Starting Member

10 Posts

Posted - 2009-10-20 : 06:59:29

Hello, first thank you for those who helped.
second i fixed it here is the solution:

SELECT item_id, deesc
   FROM FACILITY, [order] WHERE [order].item_id = "F" + Cstr(facility.facility_id)
                                   AND [order].to_date >= DATE()

this is without joins.

Share your knowledge it's one way to achieve immortality http://www.rabihtawil.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources