CODEWITHSUNDEEP
databasepostgresqlfor-loopselectrecursion
LUser
LUser

Reputation: 1194

Postgresql select from results in for loop like

My English isn't the best but I will try. I have tables with comments and replies, I am wanting to select parentids LIMIT 10 from comment and select the replies that are tied to those.

  parentid  |   replyid   |  commentowner  |         commentbody         |        postcreation        
------------+-------------+----------------+-----------------------------+----------------------------
 h0rfizsUF6 | CGTSh5XLCB  | [email protected]  | anyone want to make flowers | 2018-04-30 21:35:53.502332
            | CGTSh5XLCB  | [email protected]   | reply to mary about flowers | 2018-04-30 21:39:04.313967
            | CGTSh5XLCB  | [email protected]  | ok well sign up             | 2018-04-30 21:39:33.376884
 Zasrw8768F | DAeing34355 | [email protected] | Hey everyone!               | 2018-04-30 21:40:44.777557
 Zasr2222F  | DAeingrrrr  | [email protected]  | yo yo yo all                | 2018-04-30 21:41:33.800034
            | CGTSh5XLCB  | [email protected] | Im signed up already        | 2018-04-30 21:42:03.771954
            | DAeingrrrr  | [email protected] | in what house               | 2018-04-30 21:43:10.992619
(7 rows)

What I want:

  parentid  |   replyid   |  commentowner  |         commentbody         |        postcreation        
------------+-------------+----------------+-----------------------------+----------------------------
 h0rfizsUF6 | CGTSh5XLCB  | [email protected]  | anyone want to make flowers | 2018-04-30 21:35:53.502332
            | CGTSh5XLCB  | [email protected]   | reply to mary about flowers | 2018-04-30 21:39:04.313967
            | CGTSh5XLCB  | [email protected]  | ok well sign up             | 2018-04-30 21:39:33.376884
            | CGTSh5XLCB  | [email protected] | Im signed up already        | 2018-04-30 21:42:03.771954
 Zasr2222F  | DAeingrrrr  | [email protected]  | yo yo yo all                | 2018-04-30 21:41:33.800034
            | DAeingrrrr  | [email protected] | in what house               | 2018-04-30 21:43:10.992619

when I get the parentids I am returned with this

socialnetwork=# select parentid from comments where commentowner ='[email protected]' and parentid IS NOT NULL;
  parentid  
------------
 h0rfizsUF6
 Zasr2222F

but I cannot perform a forloop through this return.

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

Upvotes: 0

Views: 42

Answers (1)

user464502
user464502

Reputation: 2383

It's not entirely clear what you're trying to do, but perhaps something like the following:

with parents as (
    select distinct replyid
    from comments
    where commentowner ='[email protected]' and parentid IS NOT NULL
)
select C.* from parents P left join comments C on P.replyid = C.replyid
;

This aren't ten parentids, but your example table has nulls in the parentid column, and it's not clear how the rows are associated together. The identical replyid columns are confusing as well. Perhaps those should be parentid, or commentid.

In any case, you can use a CTE to construct the set of keys you're interested in, and then join against the table to get the rows you want.

Upvotes: 1

Related Questions