Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    19

    Unanswered: Don't understand EXISTS in Access query

    I must not be understanding the EXISTS keyword in Access queries. According to several websites the two following queries should yield the same number of rows, but the second one gives all the rows in the table.

    SELECT tblTXMup.*
    FROM tblTXMup, tblTXMupPlans
    WHERE tblTXMup.cID = tblTXMupPlans.cID
    AND tblTXMupPlans.eDate>#6/1/2008#

    Yields 18,382 rows

    SELECT Count(*) AS Expr1
    FROM tblTXMup
    WHERE Exists (SELECT tblTXMup.*
    FROM tblTXMup, tblTXMupPlans
    WHERE tblTXMup.cID = tblTXMupPlans.cID
    AND tblTXMupPlans.eDate>#6/1/2008#);

    Yields 46,638 rows

    I selected INTO to remove a large portion of a table into an archive table and now need to delete those same records in the original table. What am I doing wrong?

    Thanks for any help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the EXISTS clause returns a single true/false answer

    the way you've used it, the EXISTS clause checks to see whether there are any rows in that join

    if there are, the EXISTS is true, and that's why you're getting all the rows in the outer query

    try this --
    Code:
    SELECT Count(*) AS Expr1
      FROM tblTXMup
     WHERE EXISTS 
           ( SELECT 'foo'
               FROM tblTXMupPlans
              WHERE cID = tblTXMup.cID 
                AND eDate > #6/1/2008# )
    this subquery is a little different from yours -- it's a correlated subquery

    there's one table in the subquery, but its rows are correlated to the outer query's rows

    your EXISTS clause wasn't correlated, because the "copy" of the tblTXMup inside the subquery wasn't the same as the tblTXMup of the outer query (even though they had the same name)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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