CODEWITHSUNDEEP
sqlpostgresql
WHOATEMYNOODLES
WHOATEMYNOODLES

Reputation: 2643

Postgresql how to combine these two queries?

I'm trying to combine these two queries, but I can't figure out how

SELECT id FROM comments WHERE thread_id = $1 AND parent_id is NULL

This returns a integer. i.e - 5

And then use the id result (5) for this recursive query

With RECURSIVE recursetree AS
(
 SELECT * FROM comments WHERE comment_id = (use the id 5 here)
 UNION
 SELECT t.*
 From comments t
 JOIN recursetree rt ON rt.comment_id = t.parent_id                         
 )
 SELECT * from recursetree

Upvotes: 1

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You should be able to put the first query into the anchor of the recursive subquery:

With RECURSIVE recursetree AS (
      SELECT c.
      FROM comments c
      WHERE thread_id = $1 
      UNION ALL
      SELECT c.*
      FROM comments c JOIN
           recursetree
           rt ON rt.comment_id = c.parent_id                         
     )
SELECT * 
FROM recursetree;

Also note:

  • UNION ALL makes more sense than UNION, unless you want to incur the overhead of removing duplicates.
  • The table alias c makes more sense for comments than the arbitrary letter t.
  • The $1 suggests that you are munging the query string with a parameter. You should be using parameters for the query.

Upvotes: 1

Related Questions