SQL Server 'views' and between

Question

Hey sql newb here, I have this problem which I am suppose to create a view which contains the information of different bookings of a product between a certain time frame (march - April)

I came up with this, but instead of displaying the specific time frame it's just showing everything

CREATE VIEW sailors_view1 
AS
   SELECT sailors.sid, sname, rating
   FROM sailors, reserves
   WHERE start_date BETWEEN '8 March, 2007' AND '14 April, 2007'
   With Check Option
GO

and I'm using this to test: SELECT * From sailors_view1

Update: table definitions

CREATE TABLE sailors 
  (sid CHAR(5), 
   fname VARCHAR(10), sname VARCHAR(10), 
   street_address VARCHAR(20), 
   suburb VARCHAR(20), 
   rating INT, age INT, phone CHAR(3)) 

CREATE TABLE dbo.reserves 
  (sid CHAR(5), 
   bid CHAR(3), 
   start_date DATETIME, 
   end_date DATETIME, 
   rname VARCHAR(10))

can you post the table definitions of sailors and reserves please? — Matt, May 29 '11 at 05:58
create table sailors ( sid char(5), fname varchar(10), sname varchar(10), street_address varchar(20), suburb varchar(20), rating int, age int, phone char(3)) — Ari, May 29 '11 at 07:16
create table reserves ( sid char(5), bid char(3), start_date datetime, end_date datetime, rname varchar(10)) — Ari, May 29 '11 at 07:16
@Ari: please don't post stuff like this in comments - it's **really hard to read** !! Please update your original question by **editing** it to provide this additional info !! — marc_s, May 29 '11 at 07:42
Whoops sorry. Yea it creates the view fine, except the way I've coded it is wrong, it's only suppose to return one specific value which is between those two dates, but it returns the entire table information — Ari, May 29 '11 at 07:48

score 2 · Answer 1 · edited May 29 '11 at 07:31

2

You have an implicit cross join in your from clause which will certainly give you more records than you expect. You should probably either use a standard join clause (inner join, left, right, etc) or include a join condition in your where clause as in:

where sailors.sid = reserves.sid

Also, I don't recall seeing a date format like that in sql server. Is your start_date column a datetime data type, or is it a char/varchar?

Even with both of those issues, I'm not sure they would explain why you're getting all records, but I would check on them and see if you get closer to the answer.

edited May 29 '11 at 07:31

gbn

422,506
82
585
676

answered May 29 '11 at 06:02

jlnorsworthy

3,914
28
34

1

You'd use explicit JOIN..ON syntax nowadays... http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338 – gbn May 29 '11 at 07:34
@gbn I wasn't advocating the older style join, just keeping it simple for the poster – jlnorsworthy May 30 '11 at 06:08

score 1 · Answer 2 · answered May 29 '11 at 07:47

I would recommend not using the old-style JOIN with just table after table, separated by commas. This leads to unexpected results if you forget (like you did) to define a join condition in the WHERE clause.

Please always use the new ANSI-style JOIN syntax - something like this:

CREATE VIEW sailors_view1 
AS
   SELECT 
       s.sid, s.sname, r.rating
   FROM 
       dbo.sailors s 
   INNER JOIN
       dbo.reserves r ON s.sid = r.sid
   WHERE 
       r.start_date BETWEEN '20070308' AND '20070414'
   WITH CHECK OPTION
GO

With this explicit INNER JOIN and a ON s.sid = r.sid clause, you clearly state your intent of joining those two tables, and using the sid column as the common criteria to join the rows on.

Also, to be on the safe side (independent of any language or regional settings), I would always use the ISO 8601 date format - YYYYMMDD - it works in all settings and doesn't suddenly fall over if your server has a different language setting..

SQL Server 'views' and between

2 Answers2