0

Say I want to look up the ltree paths for "active" recipes, and then use the those paths to find descendants of those recipes.

# recipes
name      | category | path
-------------------------
Main        active     recipeA
Sides       inactive   recipeB
Lettuce     active     recipeC
Drinks      inactive   recipeD
abc                    recipeA.tacos
def                    recipeA.hamburgers
ghi                    recipeB.sausages
jkl                    recipeC.lettuce
mno                    recipeC.fries
pqr                    recipeD.cola

Here is my pseudo-code query (which is obviously incorrect):

SELECT name, category, path FROM recipes WHERE category='active'......then SELECT name, path FROM recipes WHERE path <@ '(IDENTIFIED|RECIPE|PATHS|HERE)'

The results would give me records with paths like:

abc         recipeA.tacos
def         recipeA.hamburgers
jkl         recipeC.lettuce
mno         recipeC.fries

Is there a way to do it correctly in one query?

sjsc
  • 4,552
  • 10
  • 39
  • 55
  • 1
    You need to provide table definition, test data corresponding to that definition - as text, **no images** and a complete SQL statement with results the results. – Belayer Aug 13 '20 at 18:44
  • Are you saying the first query would have a `WHERE` condition to find only a few paths? Currently you always get all paths, and you wouldn't need to find any "subpaths" for them. – Bergi Aug 13 '20 at 19:15
  • Bergi, I'll make it a little clearer in the question. Sorry. I'm looking to find a subset of recipes (not all of them), and then from that query for its subpaths. – sjsc Aug 13 '20 at 19:20
  • 1
    You can do it with a subquery or a self-join. Are the paths you select with your category condition always distinct? And do the descendants that you want to select always have `category IS NULL`? – Bergi Aug 13 '20 at 21:47
  • Thanks Bergi! "Inner join" to the same table solved all my troubles. Thanks a bunch again – sjsc Aug 14 '20 at 13:59

0 Answers0