Thread: Not between any

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2

    Not between any


    Hello! How can I combine the query NOT BETWEEN and ANY?

    This query has an error. Subquery returns more than 1 row. I searched google and suggest that I should use ANY.

    This is the query

    PHP Code:
    select hourtitle from hours where hourtitle not between
    (select starttime from schedules) and (select endtime from schedules
    and I want something like

    select hourtitle from hours where hourtitle not between
    ANY (select starttime from schedules) and ANY (select endtime from schedules)


    Thank you!
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    You can not have a list of values as parameter with (NOT) BETWEEN.
    What might work (untested):
    Code:
     select hourtitle from hours where hourtitle not between
    (select MIN(starttime) from schedules) and (select MAX(endtime) from schedules)
    But it depend at the types of starttime, endtime and hourtitle.
    Do you only have to look at the hour? what about days?
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    next time, please post your SQL questions in the database area.

    You can use EXISTS:
    Code:
    SELECT
    	hourtitle
    FROM
    	hours
    WHERE
    	EXISTS (
    		SELECT
    			1
    		FROM
    			schedules
    		WHERE
    			hours.hourtitle BETWEEN starttime AND endtime
    	)
    ;
    There could be much better way, so this is really a question for the SQL people. Ask a mod to move your thread.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    Originally Posted by Jacques1
    Hi,

    next time, please post your SQL questions in the database area.

    You can use EXISTS:
    Code:
    SELECT
    	hourtitle
    FROM
    	hours
    WHERE
    	EXISTS (
    		SELECT
    			1
    		FROM
    			schedules
    		WHERE
    			hours.hourtitle BETWEEN starttime AND endtime
    	)
    ;
    There could be much better way, so this is really a question for the SQL people. Ask a mod to move your thread.



    Thats it!!!=D Thank you! but what does 1 mean in the subquery?? =)
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    Originally Posted by MrFujin
    You can not have a list of values as parameter with (NOT) BETWEEN.
    What might work (untested):
    Code:
     select hourtitle from hours where hourtitle not between
    (select MIN(starttime) from schedules) and (select MAX(endtime) from schedules)
    But it depend at the types of starttime, endtime and hourtitle.
    Do you only have to look at the hour? what about days?
    Hi! Thank you for your reply. The answer posted by Jacques is what I am looking for! thank you anyway!!!=)

IMN logo majestic logo threadwatch logo seochat tools logo