Re: Exists

From: Michael C# (xyz_at_abcdef.com)
Date: 02/07/05 

Date: Sun, 6 Feb 2005 23:04:04 -0500

I see what you're trying to say. You need to relate the queries yourself in
the WHERE clause of the subquery, something like this:

SELECT *
FROM table1
WHERE EXISTS (SELECT * FROM table2 WHERE table2.field1 = table1.field1)

The correlation you're looking for is between a column in the table1 outer
query and a column in the table2 subquery. You introduce that via the WHERE
clause of the subquery. I can't imagine too many situations where you would
add an EXISTS predicate with a subquery that didn't relate to your outer
query, at least none that couldn't be better modeled using a simple JOIN in
the outer query.

Thanks,
Michael C#

"Alan" <NOSPAMalan_pltse@yahoo.com.au> wrote in message
news:eoE9neMDFHA.464@TK2MSFTNGP15.phx.gbl...
> Actually, I know the EXISTS return FALSE when now records in subquery and
> TRUE when there is records returned.
> The thing I in the dark is using this EXISTS predicate, how the subquery
> relate to the outer query ?
>
> SELECT *
> FROM table1
> WHERE EXISTS ( SELECT * FROM table2 WHERE table2.field1 = blah)
>
> The subquery has no relationship with the outer table1.
>
>> Consequently, the output is the same as long as at least one row
>> satisfies
>> the criteria. The EXISTS condition is false when no rows match.
>
>

Relevant Pages

  • Re: Other form of CTRL+APOSTROPHE ()
    ... Provided you have a primary key field in the table so that the sorting order is defined, you can execute an Update query that contains a subquery that gets the most recent non-null Part# for each row. ... This example assumes a table named Table1, with an Autonumber named ID to define the sort order: ... Part# Desc QTY UOM ...
    (microsoft.public.access.modulesdaovba)
  • Re: IsNull and aggregate functions
    ... Firstly this my first time posting to technical groups - so any ... from table1 t1 ... In the first subquery, I fail to see the reason for adding a GROUP BY ... user are in table3, then this query has the risk of resulting in more ...
    (comp.databases.ms-sqlserver)
  • Re: Querry not given right results
    ... Let's concentrate on the subquery starting in line 4. ... within that subquery you reference the instance of TABLE1 ... instance of TABLE1 in the inner query. ...
    (microsoft.public.access.queries)
  • Re: Other form of CTRL+APOSTROPHE ()
    ... I have looked at your subquery page and am still a bit lost as to where the ... FROM AS Dupe ... This example assumes a table named Table1, with an Autonumber named ID to ... Part# Desc QTY UOM ...
    (microsoft.public.access.modulesdaovba)
  • Re: Delete duplicates from a table
    ... duplicates defined by identical values in Field2 and Field3. ... It keeps the one with the lowest primary key value. ... DELETE FROM Table1 ... If more fields are involved in what defines "duplicate", just add more to the WHERE clause in the subquery. ...
    (microsoft.public.access.queries)