Postgresql select from results in for loop like

Question

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

user464502 · Accepted Answer · 2018-04-30 21:52:29Z

1

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.

answered Apr 30, 2018 at 21:52

user464502

2,4631 gold badge13 silver badges15 bronze badges

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

2 Comments

LUser Over a year ago

The replyis from the parent is what ties them together. The parentid identifies a sort of topic. This way the parent can identify it's children. Let me try it out.

LUser Over a year ago

Ok this was pretty close . It set me on the right path . Your select did everything corect except for the order was off somewhat. This seems to fix it completely. ::: 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 order by replyid,postcreation;

Collectives™ on Stack Overflow

Postgresql select from results in for loop like

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related