W
W
WhatIsHTML2018-08-29 13:36:25
PostgreSQL
WhatIsHTML, 2018-08-29 13:36:25

How to combine two queries into one Postgresql?

The task is to select a list of people from the "people" table according to a certain condition, but at the same time, check if the person from the selected list is added as a friend in another table "user_friends".
It can be done with two queries:
1. Get a list of users from the "people" table

SELECT * FROM people 
WHERE people.name LIKE $(regex) 
LIMIT $(limit);

2. We are looking for user IDs in the "user_friends" table.
SELECT * 
FROM user_friends 
WHERE user_friends.user_id = $(user_id) AND user_friends.friend_id IN ($1:csv)

How to combine them into one?
Requests above for schematic explanation, may contain errors

Answer the question

In order to leave comments, you need to log in

2 answer(s)
D
Dmitry Telepnev, 2018-08-30
@TelepnevDmitriy

WITH peoples AS (
    SELECT * FROM people 
    WHERE people.name LIKE $(regex) 
    LIMIT $(limit)
)
SELECT f.* 
FROM user_friends f
JOIN peoples p.id = f.friend_id
WHERE user_friends.user_id = $(user_id)

R
Rsa97, 2018-08-29
@Rsa97

WHERE `id` IN (SELECT ...)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question