If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. A subquery used in this way become a temporary table, and you must provide a table alias name for the subquery as in "SELECT ... FROM (SELECT ...) aliasName". The following statement shows you how to use a subquery as base table for the main query:

SELECT * FROM (SELECT l.id, l.url, r.comment

FROM ggl_links l LEFT OUTER JOIN ggl_rates r

ON l.id = r.id) WHERE url LIKE '%er%'

GO

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'WHERE'.

SELECT * FROM (SELECT l.id, l.url, r.comment

FROM ggl_links l LEFT OUTER JOIN ggl_rates r

ON l.id = r.id) s WHERE s.url LIKE '%er%'

GO

101 www.rendc.org The best

102 www.rendc.org/html Well done

103 www.rendc.org/xml Thumbs up

107 www.rendc.org/sql NULL

The error on the first query is caused by the missing alias name to name output of the subquery as a temporary table.