Results 1 to 6 of 6

Thread: Query issue

  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: Query issue

    Hello all,

    I'm new here and I have a question. There is a query im running which fails to execute on our servers. We run MYSQL 5 locally, while our server runs 4.0.25. What puzzels me is why the code is not running. The code is as follows:

    select DISTINCT id from [] where []=1 and NOT EXISTS (select [] from [] where []=[]);

    but the server tells us the following :

    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (select [] from [] where []=[]) LIMIT

    Can someone tell me whether mysql 4 supports NOT EXISTS? We even tried NOT IN, but no success...

    Thank You.

    Pirox

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    subqueries are not supported until version 4.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think what you need is more like:
    Code:
    select id
       from []
       LEFT JOIN []
          ON ([] = [])
       where 1 = []
          AND [] IS NULL
       GROUP BY [];
    This should work much better in MySQL.

    -PatP
    Last edited by Pat Phelan; 06-12-06 at 12:52.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what the hell i sthat?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Doesn't []=[] always evaluate to true? I'm gonna plump for:
    Code:
    SELECT DISTINCT id 
    FROM [] 
    WHERE []=1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    Doesn't []=[] always evaluate to true? I'm gonna plump for:
    Code:
    SELECT DISTINCT id 
    FROM [] 
    WHERE []=1
    Good catch on the [] = 1 part! I missed that, so I've amended my original suggestion.

    I don't think it is safe to assume that [] will always equal [], so I think it is better left stated explicitly. If it isn't needed, I'm sure that it will get optimized out.

    -PatP

Posting Permissions

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