Results 1 to 6 of 6

Thread: Exists -- ?

  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Unanswered: Exists -- ?

    Select field1 from table1 where exists (select fields1from table2);

    The above SQL generates error: "#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 field1 FROM table2 ) LIMIT 0, 30' at line 3

    But the sql is an example in MySQL manual and it passed the test on MS ACCESS. I also tried to change 'exists' to
    'field1 in' or 'field1 = any' or 'fields is ', none of them works.

    Any idea?

    Shawn

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on which version of MySQL you are using, the EXISTS clause may not be supported. In almost any version you could use:
    Code:
    SELECT a.field1
       FROM table1 AS a
       INNER JOIN table2 AS b ON (b.field1 = a.field1)
       LIMIT 0, 30;
    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't forget to add DISTINCT to pat's query when there's a one-to-many relationship between table1 and table2

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good point! Thanks Rudy.

    -PatP

  5. #5
    Join Date
    Jun 2004
    Posts
    6

    re:

    Thank you so much to both of you. Your query works. What is the meaning of 'limit 0, 30'?

    BTW, the sql actually has DISTINCT but I removed it for asking the question.

    thanks again.
    Shawn

  6. #6
    Join Date
    Jun 2004
    Posts
    6
    I know the meaning of 'limit 0, 30' now.

    thanks;
    Shawn

Posting Permissions

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