asked
Removing Repeated Identical SubQueries From A Query?
Hi there, I have a query where the subquery is identical and repeated in both the WHERE and the SELECT clauses. Is it possible to remove this repeation from querying TABLE2 twice? SELECT CLIENT, (SELECT PUB FROM TABLE2 WHERE VALID = 1 AND DATE
That subquery does not seem to be correlated with the outer query. If there is more than one row where valid = 1 and that date is before today (also note that date is a reserved word in recent versions of SQL), It will return an error about subqueries returning more than 1 value. But to directly answer your question, in recent versions of SQL Server (2005 and later) you could use a CTE to avoid repeating it. In SQL Server 2000, you could consider turning that subquery into a view. Also, if that subquery is expected to return exactly one result, as the structure of your query implies, and you could use a script instead of a single query, you could just stuff the one value it returns into a variable and then use the variable in your main query.
This seems to be the case of poor query design. You must re-design it according to the logic. If I understand correctly, you want to make sure that there must be one row (and exactly one) from TABLE2 which seems to be a look-up table with having only one row for criteria VALID = 1.... To me your query should be like this SELECT CLIENT , PUB FROM TABLE1 CROSS JOIN ( SELECT PUB FROM TABLE2 WHERE VALID = 1 AND DATE
