How to combine two queries in SQL? (subquery)-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You have not mentioned what DBMS you are using but usually you can use something like IS IN. See this example from the PostgreSQL docs. Combine that with a LIMIT and it should yield what you're looking for.

The final query would look something like

SELECT aboutme
FROM users
WHERE id IN (
   SELECT owneruserid
   FROM posts
   WHERE title LIKE LOWER('%html%')
   ORDER BY score DESC
   LIMIT 50
)

You can also use a CTE instead of putting the subquery inside the WHERE statement for better readability.

Also depending on how you would want to handle the case where one user has two top 50 posts you need to add a DISTINCT to the query to get only unique user_ids