CODEWITHSUNDEEP
sqlpostgresql
Bunnies_Nothing
Bunnies_Nothing

Reputation: 5

Use a subquery and a custom value with IN operator

I can't really find any answer that fits my question.

I need to know how I can be able to use a subquery together with a custom value with the IN Operator in SQL. Here is an example of a query I came up with:

SELECT posts.content, users.username,users.name, users.verified
FROM posts, users 
WHERE users.uid = posts.post_by 
AND posts.post_by IN ((SELECT user_uid FROM user_data.user1_following), 'USER_UID')
ORDER BY posts.id DESC;

This query is meant to show posts only from users in a users' following table - And it should also show posts from the original user themselves.

post_by represents a users UID(Unique ID)

This query works if the user is only following one person. If they follow multiple people, an error is returned. It states the following:

more than one row returned by a subquery used as an expression

I know this is coming from the block:

((SELECT user_uid FROM user_data.user1_following), 'USER_UID')

How do I go about getting the result I'm looking for by using both an subquery and a custom value ('USER_UID')? Alternative methods are welcome as long as they produce the result I'm looking for.

Upvotes: 0

Views: 224

Answers (2)

PM 77-1
PM 77-1

Reputation: 13334

You can use UNION inside your IN clause as long as user_uid has Character type:

IN (SELECT user_uid FROM user_data.user1_following
      UNION
    SELECT 'USER_UID')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use JOIN !!!!

I find your logic a bit unclear -- I'm not sure whether you want "followers" or "following". But the idea is:

SELECT p.content, u.username, u.name, u.verified
FROM posts p JOIN
     users u
     ON u.uid = p.post_by 
WHERE p.post_by = $user_uid OR
      EXISTS (SELECT 1
              FROM user_data ud
              WHERE ud.user1_following = $user_uid AND
                    ud.user_uid = p.post_by 
             );
ORDER BY p.id DESC;

Upvotes: 2

Related Questions