Search code examples
t-sqldataexplorer

How to use the results of one query to compose another query?

I am trying to query satckoverflow data at https://data.stackexchange.com/stackoverflow

SELECT Id as postId, PostTypeId, OwnerUserID, AcceptedAnswerId from posts 
where PostTypeId = 1

will give me a list of 50,000 questions and user Id of the user who posted the question (OwnerUserId) and the user Id of the person whose answer was accepted (AcceptedAnswerId).

Now I am trying to write a query to select a list of users who have either asked or posted the accepted answer. Something like this:

Select Id, reputation from users 
INNER JOIN
(Select Id as postId, OwnerUserId, AcceptedAnswerId from posts)
ON
users.Id = posts.OwnerUserId or users.Id = posts.AcceptedAnswerId
Solution

  • The answer to the question in the title is: you can do this with a WITH clause.

    Trivial example:

    WITH query1 AS
(SELECT * FROM myTable),
query2 AS
(SELECT * FROM query1)
SELECT * FROM query2;

    WITH clauses greatly enhance readability of complex queries.