How to combine two queries in SQL? (subquery)

December 30, 2021

Issue

I am having trouble combining two SQL queries into one. I want to use part of the data from the first query to reach into a different table and retrieve some more information.

Description

I have the following query that gets the top 50 Stackoverflow posts that contain the word "html" in the title:

SELECT  top 50
        Id AS [Post Link],
        Title,
        Score,
        OwnerUserId
FROM    Posts
WHERE  Title LIKE lower('%html%')
ORDER BY
        Score DESC

When I have retrieved this data, I’d like to take the 50 OwnerUserIds and query the Users table for their AboutMe and display it with the result from the "main query". As I understand it, this can be achieved using a subquery, but I’m not quite sure how. What I want to do is:

SELECT AboutMe 
FROM Users 
WHERE Id = OwnerUserId (having taken the OwnerUserId from the first query)

Any help is greatly appreciated

>Solution :

I don’t think you want a subquery which would probably look something like

SELECT AboutMe 
FROM Users 
WHERE Id in
(
   SELECT  top 50 
   OwnerUserId
   FROM    Posts
   WHERE  Title LIKE lower('%html%')
   ORDER BY
        Score DESC
)

I think you really want one query to return all the results by joining the two tables, e.g. something like

SELECT  top 50
        Posts.Id AS [Post Link],
        Posts.Title,
        Posts.Score,
        Posts.OwnerUserId,
        Users.AboutMe
FROM    Posts
Inner join users on users.id = Posts.OwnerUserId
WHERE  Title LIKE lower('%html%')
ORDER BY
        Posts.Score DESC