Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    56

    Unanswered: SQL - Date problem

    Hi,

    I am trying to create a sql statement for the following:

    I have a Video database- a film table and a rentals table which are linked by filmid
    Within Rentals table I have Date_Returned and Date_of_rental as fields (date datatypes).

    I am trying to create a query will return the film titles for rentals where the date_returned minus sysdate is greater than 2 or where date_returned minus date_of_rental is greater than 2. I am getting a multiplicative effect... Can anyone tell me what I am doing wrong?!

    select f.title
    from film f, rentals r
    where (sysdate - date_returned) > 2
    or (date_returned - date_of_rental) >2
    and f.filmid = r.filmid

    Regards,

    C.

  2. #2
    Join Date
    Jan 2003
    Posts
    56

    Re: SQL - Date problem

    Sorry - I put this in the wrong forum. Apologies!

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL - Date problem

    You are getting each film x number of rentals matching the rule. A simple fix would be SELECT DISTINCT ...

    Or you could say:

    select f.title
    from film f
    where filmid in
    ( select filmid from rentals
    where (sysdate - date_returned) > 2
    or (date_returned - date_of_rental) >2
    );

  4. #4
    Join Date
    Jan 2003
    Posts
    56

    Re: SQL - Date problem

    Originally posted by andrewst
    You are getting each film x number of rentals matching the rule. A simple fix would be SELECT DISTINCT ...

    Or you could say:

    select f.title
    from film f
    where filmid in
    ( select filmid from rentals
    where (sysdate - date_returned) > 2
    or (date_returned - date_of_rental) >2
    );
    Thanks a million!!!

    That works perfectly,

    C.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •