How to use a Query in place of a Table in SQL Server

Developers can select rows from a query. For example, suppose they have already written a query retrieving patents and identifiers of the coinventorred patents — the patents with more than one inventor. The SQL might look like this:

 

SELECT

patents.patent_id,

description,

area

FROM

patentinventor

INNER JOIN

inventors

ON patentinventor.patent_id

=  inventors.patent_id

GROUP BY

patents.patent_id,

description,

area

HAVING COUNT(*) > 1

 

Developers can then write another query that builds on this result. For example, they can write a query that retrieves the coinventorred avionic patents. To write this new query, they can use the existing query as the source of the new query’s data. The resulting SQL might look like this:

 

SELECT

description

FROM

(

SELECT

patents.patent_id,

description,

area

FROM

patentinventor

INNER JOIN

inventors

ON patentinventor.patent_id

=  inventors.patent_id

GROUP BY

patents.patent_id,

description,

area

HAVING COUNT(*) > 1

)

co_inventorred_patents

WHERE area = ‘avionic’

 

Similarly, a query can participate in a JOIN operation. For example, developers can find the opened coinventorred patents merely by joining the OpenedPatents view to the query retrieving the coinventorred patents. The resulting SQL might look like this:

 

SELECT

OpenedPatents.description

FROM

OpenedPatents

INNER JOIN

(

SELECT

patents.patent_id,

description,

area

FROM

patentinventor

INNER JOIN

inventors

ON patentinventor.patent_id

=  inventors.patent_id

GROUP BY

patents.patent_id,

description,

area

HAVING COUNT(*) > 1

)