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:

C# 8.0 and .NET Core 3.0 – Modern Cross-Platform Development: Build applications with C#, .NET Core, Entity Framework Core, ASP.NET Core, and ML.NET using Visual Studio Code, 4th Edition
ASP.NET Core 3 and Angular 9: Full stack web development with .NET Core 3.1 and Angular 9, 3rd Edition
C# in Depth, 4th Edition

 

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

)