SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    checking existance in a list

    Is there a list facility in SQL. I'm trying to look for ID numbers that are not in a list.

    i.e.

    select myID
    from myTable
    where myID {doesn't exist in this list}

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    A query like the one below should do the trick:

    Code:
    SELECT myID FROM myTable
      WHERE myID NOT IN(2,6,3)
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    here's a tip: never write ... WHERE NOT id IN ( 2,6,3 )

    this works exactly as expected in other databases, but not (no pun intended) in mysql

    mysql has some weird logic, and this example is amongst the worst
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for getting back to me.
    I've a quick question, if my id=2 would it be counted in the list if there was a value of 22,?

    I'm hoping not but I've come across this problem before with other programming.

  5. #5
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    What the IN function does is to check if the specified value (in this case, the value of a the column called myID) exists in a comma-separated list. You could compare this to using in_array in PHP.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry you've lost me I don't use PHP.

    Would:

    SELECT myID FROM myTable
    WHERE myID NOT IN(22,6,3)

    Would this pick up myID=2 as being in the list or not?

    Thanks,


    Richard

  7. #7
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by rcuser
    Would this pick up myID=2 as being in the list or not?
    The answer is no, it will not pick out myID=2.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    London
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, Thanks for your help.

    Richard

  9. #9
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In MySQL, the following query will select all of the values in the myID field,

    Code:
      SELECT myID FROM myTable
        WHERE myID <> ANY (22,6,3)
    as long as those values are not in the list above.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by asterix
    In MySQL, the following query will select ...
    not in mysql 4.0 it won't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    not in mysql 4.0 it won't
    What is their problem at MySQL? First they come up with brain dead syntax (don't even get me started on their multi-table update syntax) but they don't even make it backwards compatible!

    That really annoys me, first bringing out truly odd syntax. Then not supporting it.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    are you saying this does work in a release later than 4.0?

    i can't test it, because i can't install anything myself

    and on which planet does any software vendor introduce a new feature in a new release and then go back and make it work in all previous versions???????????????

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    maybe you were thinking of ANY (subquery)

    http://dev.mysql.com/doc/mysql/en/an...ubqueries.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe you were thinking of ANY (subquery)

    Yep... My mistake.

    I don't have mySQL 4.0 either.
    What about this?
    Code:
      SELECT myID FROM myTable
    	WHERE myID <> ANY (select myid from mytable where myid in (1,2,3) )
    or

    Code:
      SELECT myID FROM myTable
     	WHERE myID <> 1 and myid <>2 and myid <>3

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well your first one won't work in 4.0 because it uses a subquery

    and your second one works but is needlessly convoluted when a simple IN list is so much simpler

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, stupid me. Lilleman already posted the solution:

    http://www.sitepoint.com/forums/show...96&postcount=2


Bookmarks

Posting Permissions

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