Comments and answers for "Removing Repeated Identical SubQueries From A Query?" The latest comments and answers for the question "Removing Repeated Identical SubQueries From A Query?" Comment by David 2 1 on David 2 1's answer Thanks yes you are right it is poor design which I've inherited and looking to change. Your assumption are also correct in that there must and will only ever be one row returned. Away to test your code. Thanks. Fri, 11 Nov 2011 03:10:39 GMT David 2 1 Answer by Usman Butt 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 <= GETDATE() --AND PUB IS NOT NULL /* Is this required? It depends upon the logic */ ) AS TABLE2 WHERE SEND = 1 Thu, 10 Nov 2011 23:27:47 GMT Usman Butt Answer by TimothyAWiseman 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. Thu, 10 Nov 2011 10:22:44 GMT TimothyAWiseman