PostgreSQL loop through subquery

Question

I'd like to loop through the results of a select subquery and use them yo fetch data off of another table:

SELECT alias, profile_picture 
FROM public.profile 
where user_id = (
    SELECT friend_user_id 
    FROM public.friends 
    where user_id = 1059948007363671)::text

obviously this query returns:

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

What would be the best way to loop through the subquery results and assign it to a temporary variable that pubic.profile could use to fetch the respective profile info?

SELECT alias, profile_picture FROM public.profile JOIN public.friends on profile.user_id=friends.friend_user_id WHERE friends.user_id = 1059948007363671 — Mihai
– Mihai, Commented Dec 28, 2015 at 22:02
yea that did the trick =) thanks. I cant vote for your answer since its a comment. maybe you wanna re-post it as a questiion?? — Jose Maria Landa
– Jose Maria Landa, Commented Dec 29, 2015 at 3:22

Andomar · Accepted Answer · 2015-12-28 21:53:27Z

2

Use in instead of =:

WHERE user_id IN 
    (
    SELECT friend_user_id::text
    FROM public.friends 
    WHERE user_id = 1059948007363671
    )

answered Dec 28, 2015 at 21:53

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihai · Accepted Answer · 2015-12-29 04:22:00Z

1

SELECT alias, profile_picture 
FROM public.profile 
JOIN public.friends 
ON profile.user_id=friends.friend_user_id 
WHERE friends.user_id = 1059948007363671

answered Dec 29, 2015 at 4:22

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

PostgreSQL loop through subquery

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related