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

Topic

New Reply

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]., [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);

Reply | Quote

Replies

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

---

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

Reply | Quote

Thanks for the info Charlotte.

Can I access the TechNet data online?

Reply | Quote

http://www.msdn.microsoft.com

Reply | Quote