7

I have a table in PostgreSQL database that contains parts of addresses in a form of a tree and looks like this:

Id | Name         | ParentId
1  | London       | 0
2  | Hallam Street| 1
3  | Bld 26       | 2
4  | Office 5     | 3

I would like to make a query to return an address, concatenated from all ancestor names. I need the result table to be like this:

Id | Address
1  | London
2  | London, Hallam Street
3  | London, Hallam Street, Bld 26
4  | London, Hallam Street, Bld 26, Office 5

I guess I have to use WITH RECURSIVE query, but all the examples I've found use the where clause, so I have to put WHERE name='Office 5' to get the result only for that particular row. But I need a concatenated address for each row of my initial table. How can this be done?

mofoyoda
  • 695
  • 2
  • 10
  • 16

1 Answers1

10

The trick with recursive queries is that you need to specify a seed query. This is the query that determines your root node, or the starting point to descend or ascend the tree that you are building.

The reason the WHERE clause is there is to establish the seed ID=1 or Name=Bld 26. If you want every record to have the tree ascended or descended (depending on what you specify in the unioned select), then you should just scrap the WHERE statement so all records are seeded.

Although, the example you give... you might want to start with WHERE ID=1 in the seed, write out the child ID and parent ID. Then in the Union'd SELECT join your derived Recursive table with your table from which you are selecting and join on the Derived Recursive table's Child to your table's parent.

Something like:

WITH RECURSIVE my_tree AS (
  -- Seed

  SELECT 
      ID as Child, 
      ParentID as Parent, 
      Name,
      Name as Address
  FROM <table>
  WHERE <table>.ID = 1


  UNION

  -- Recursive Term
  SELECT 
    table.id as Child, 
    table.parent_id as Parent, 
    table.name,
    t.address || ', ' || table.name as Address

  FROM my_tree as t 
    INNER JOIN <table> table ON
        t.Child = table.Parent_Id  
)
SELECT Child, Address from my_tree;

I've not used PostgreSQL before, so you might have to fuss a bit with the syntax, but I think this is pretty accurate for that RDBMS.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • The parentheses around the "seed" query are useless (and confusing in my personal opinion). Additionally string literals need to be enclosed in single quotes (`','`) in SQL, not double quotes (double quotes are for identifiers) –  Oct 10 '14 at 12:48
  • Thanks, @a_horse_with_no_name! I thought the parantheses around the seed were strange. I was working from a postgresql sample I found online and thought that perhaps it cared. I use Teradata and it looks like the syntax is nearly identical, except it would throw a fit if each field wasn't explicitly declared in the WITH RECURSIVE clause. – JNevill Oct 10 '14 at 12:53
  • It seems to be a common misconception that a union (or union all) query needs parentheses, so I'm not surprised you found that online somewhere. I don't know where that comes from though –  Oct 10 '14 at 12:55
  • To be honest, the example had the seed wrapped in double parentheses. While I've never used PostgreSQL I refused to believe that it demanded double parentheses around anything. I've heard too many good things about it. – JNevill Oct 10 '14 at 13:00