Removing Repeated Identical SubQueries From A Query?

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 <= GETDATE()
                            --AND PUB IS NOT NULL /* Is this required? It depends upon the logic */
                   ) AS TABLE2
WHERE   SEND = 1