Results 1 to 4 of 4
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    SQL - Exists Reserve Word Returned on a Subquery (A2K - SR1)

    I have the following SQL statement that is used to extract data from a table. S is a text field. GID and SC are Integers. The statement will extract S, GID, and SC for the greatest SC for each GID.

    When I run the query, I receive the following message:

    You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

    Where can I find data on the EXISTS reserved word. I searched the Help File and Microsoft Knowledge base but have not had any luck. Can the below statement be used to retrieve the results.

    SELECT Table1.S, TABLE1.GID, TABLE1.SC
    FROM TABLE1
    WHERE (TABLE1.GID, TABLE1.SC) IN(
    SELECT Table1.GID, Max(Table1.SC) AS MaxOfSC
    FROM Table1
    GROUP BY Table1.GID);


    Please note that the following SQL script does work, however, I am curious to find out about the Exists reserve word.

    SELECT [TABLE1].[S], [TABLE1].[GID], [TABLE1].[SC]
    FROM TABLE1
    WHERE TABLE1.GID&"_"&TABLE1.SC IN (SELECT TABLE1.GID&"_"& Max(TABLE1.SC) AS MaxOfSC FROM TABLE1 GROUP BY TABLE1.GID);
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL - Exists Reserve Word Returned on a Subquery (A2K - SR1)

    Your error will go away if your subquery returns only a single field. You're trying to return two values, GID and MaxOfSC, and that isn't how you use subqueries.

    It isn't exactly easy to find information on EXISTS. Here's a paragraph from an article on advanced queries and SQL I found in TechNet<blockquote><hr>Existence Tests

    The existence test checks to see if the specified value from the current record exists in the subquery result. Unlike the earlier subqueries, existence tests return only a true or false. If the result returned is true, then the current record is included in the final result. If the result is false, then the current record is excluded from the final result.
    Since the result can only be true or false, the * (asterisk) wildcard is allowed. In fact, using the wildcard is more common than using a column name.
    Let
    Charlotte

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL - Exists Reserve Word Returned on a Subquery (A2K - SR1)

    Thanks for the info Charlotte.

    Can I access the TechNet data online?
    Regards,

    Gary
    (It's been a while!)

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL - Exists Reserve Word Returned on a Subquery (A2K - SR1)

    <A target="_blank" HREF=http://www.msdn.microsoft.com>www.msdn.microsoft.com</A>

Posting Permissions

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