How do I query a table with many trees of data, for one tree?

Question

I have a the NAICS schema loaded and all is working well. However I'm confused as how I'd query it in Postgrseql?

The table looks something like this,

=========NAICS_2012=========
naics | naics_parent | title
----------------------------

This is what I've wrote,

WITH RECURSIVE search_graph(naics12, naics12_parent, title, depth, path_info) AS (
        SELECT naics12, naics12_parent, title, 1, array[naics12]
        FROM naics.codes
        WHERE naics12_parent IS NULL
UNION ALL
        SELECT c.naics12, c.naics12_parent, c.title, sg.depth+1, sg.path_info||c.naics12
        FROM naics.codes AS c, search_graph AS sg
        WHERE c.naics12_parent = sg.naics12
)
SELECT * FROM search_graph ORDER BY path_info;

This produces output like this (be sure to scroll to the end)

 naics12 | naics12_parent |                                                         title                                                          | depth |         path_info          
---------+----------------+------------------------------------------------------------------------------------------------------------------------+-------+----------------------------
 11      |                | Agriculture, Forestry, Fishing and Hunting                                                                             |     1 | {11}
 111     | 11             | Crop Production                                                                                                        |     2 | {11,111}
 1111    | 111            | Oilseed and Grain Farming                                                                                              |     3 | {11,111,1111}
 11111   | 1111           | Soybean Farming                                                                                                        |     4 | {11,111,1111,11111}
 111110  | 11111          | Soybean Farming                                                                                                        |     5 | {11,111,1111,11111,111110}
 11112   | 1111           | Oilseed (except Soybean) Farming                                                                                       |     4 | {11,111,1111,11112}
 111120  | 11112          | Oilseed (except Soybean) Farming                                                                                       |     5 | {11,111,1111,11112,111120}
 11113   | 1111           | Dry Pea and Bean Farming                                                                                               |     4 | {11,111,1111,11113}
 111130  | 11113          | Dry Pea and Bean Farming                                                                                               |     5 | {11,111,1111,11113,111130}
 11114   | 1111           | Wheat Farming                                                                                                          |     4 | {11,111,1111,11114}
 111140  | 11114          | Wheat Farming                                                                                                          |     5 | {11,111,1111,11114,111140}
 11115   | 1111           | Corn Farming                                                                                                           |     4 | {11,111,1111,11115}
 111150  | 11115          | Corn Farming                                                                                                           |     5 | {11,111,1111,11115,111150}
 11116   | 1111           | Rice Farming                                                                                                           |     4 | {11,111,1111,11116}
 111160  | 11116          | Rice Farming                                                                                                           |     5 | {11,111,1111,11116,111160}
 11119   | 1111           | Other Grain Farming                                                                                                    |     4 | {11,111,1111,11119}
 111191  | 11119          | Oilseed and Grain Combination Farming                                                                                  |     5 | {11,111,1111,11119,111191}
 111199  | 11119          | All Other Grain Farming                                                                                                |     5 | {11,111,1111,11119,111199}
 1112    | 111            | Vegetable and Melon Farming                                                                                            |     3 | {11,111,1112}
 11121   | 1112           | Vegetable and Melon Farming                                                                                            |     4 | {11,111,1112,11121}
 111211  | 11121          | Potato Farming                                                                                                         |     5 | {11,111,1112,11121,111211}
 111219  | 11121          | Other Vegetable (except Potato) and Melon Farming                                                                      |     5 | {11,111,1112,11121,111219}
 1113    | 111            | Fruit and Tree Nut Farming                                                                                             |     3 | {11,111,1113}
 11131   | 1113           | Orange Groves                                                                                                          |     4 | {11,111,1113,11131}
 111310  | 11131          | Orange Groves                                                                                                          |     5 | {11,111,1113,11131,111310}

However, what I want is to see one tree, so I can write a query like,

SELECT * FROM search_graph WHERE naics12 = <foo>;

And, get a tableset back as if the table was only preloaded with the parents of the supplied NAICS12 code.

Any idea on how on further manipulate the data to get this kind of result out?

Answer 1

This answer comes from RhodiumToad (as usual) from irc.freenode.net/#postgresql,

WITH RECURSIVE search_graph(naics12, naics12_parent, title, depth, path_info) AS (
  SELECT naics12, naics12_parent, title, 1, array[naics12]
  FROM naics.codes
  WHERE naics12 = ?
UNION ALL
  SELECT c.naics12, c.naics12_parent, c.title, sg.depth+1, sg.path_info||c.naics12
  FROM naics.codes AS c
  JOIN search_graph AS sg
    ON ( c.naics12 = sg.naics12_parent )
)
SELECT * FROM search_graph ORDER BY path_info DESC;

Essentially, it was all right except the terminal conditions needs to be

WHERE naics12 = ?

And the recursive condition needs to go the other way. Rather than from the top down, I need to go from the bottom up.

ON ( c.naics12 = sg.naics12_parent )

Not,

WHERE c.naics12_parent = sg.naics12