question

David 2 1 avatar image
David 2 1 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 t-sqlsql-server-2000selectsub-querywhere
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David 2 1 avatar image David 2 1 commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.