Recursive chain with JOIN element?

Question

I recently asked for a recursive query in postgreSQL. The answer fit well, but as soon as I add a JOIN element, it gives me the following error:

ERROR: missing FROM-clause entry for table "n": WITH RECURSIVE chain AS ( SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion FROM pord n LEFT JOIN pordnrzu pz ON pz.pordnr = n.pordnr WHERE n.pordnr = 112 UNION ALL SELECT n.pordnr, pz2.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion FROM chain c LEFT JOIN pordnrzu pz2 ON pz2.pordnr = n.pordnr INNER JOIN pord n ON (c.pordnrzu = n.pordnr) WHERE c.abschl IS NULL ) SELECT * FROM chain c WHERE c.abschl IS NOT NULL

I tried several modifications but I can't get it to work. All I want is, that pordnrzu is being implemented to look for another pordnr.

So: pordnr -> pordnrzu -> pordnr

Is there anything I just missed?

This is the sample table:

CREATE TABLE pord (
  pordnr integer primary key,
  abschl text,
  stg text,
  kzfa text,
  pversion text
);

INSERT INTO pord (pordnr, abschl, stg, kzfa, pversion)
VALUES
(112,     NULL, NULL, NULL, NULL),
(140,     NULL, NULL, NULL, NULL),
(200,     NULL, NULL, NULL, NULL),
(210,     'f2', '140', 'H', '2011'),
(220,     'f2222', '140000', 'HHH', '201111');

CREATE TABLE pordnrzu (
  pordnr integer primary key,
  pordnrzu integer
);

INSERT INTO pordnrzu (pordnr, pordnrzu)
VALUES
(112,     140),
(140,     210),
(200,     220),
(210,     220),
(220,     NULL);

And this my query by now:

WITH RECURSIVE chain AS
(
  SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM pord n
  LEFT JOIN pordnrzu pz
  ON pz.pordnr = n.pordnr
  WHERE n.pordnr = 112
  UNION ALL
  SELECT n.pordnr, pz2.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM chain c
  LEFT JOIN pordnrzu pz2
  ON pz2.pordnr = n.pordnr
  INNER JOIN pord n ON (c.pordnrzu = n.pordnr)
  WHERE c.abschl IS NULL
)
SELECT *
FROM chain c
WHERE c.abschl IS NOT NULL;

My aim is that I start with pordnr=112, which then leads per pordnrzu to other pordnr until it finds the first entry with abschl NOT NULL

In this example, the solution would be this row:

pordnr   pordnrzu   abschl   stg   kzfa   pversion
200      210        f2       140   H      2011

SELECT version();

PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit

Erwin Brandstetter · Accepted Answer · 2014-02-18T15:58:26.890

1

JOIN clauses are evaluated left-to-right. Move the second join up to make this work:

WITH RECURSIVE chain AS (
  SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM   pord n
  LEFT   JOIN pordnrzu pz ON pz.pordnr = n.pordnr
  WHERE  n.pordnr = 112

  UNION ALL
  SELECT n.pordnr, pz.pordnrzu, n.abschl, n.stg, n.kzfa, n.pversion
  FROM   chain c
  JOIN   pord  n ON n.pordnr = c.pordnrzu
  LEFT   JOIN pordnrzu pz ON pz.pordnr = n.pordnr
  WHERE  c.abschl IS NULL
)
SELECT *
FROM   chain c
WHERE  c.abschl IS NOT NULL;

Alternatively, you could rewrite the 1st JOIN in the 2nd SELECT:

LEFT   JOIN pordnrzu pz ON pz.pordnr = c.pordnrzu

edited Feb 18 '14 at 15:58

answered Feb 18 '14 at 15:27

Erwin Brandstetter

605,456
145
1,078
1,228

Ok, it works, thanks for that! Could you please explain me why I need a second JOIN in the second SELECT? That'd be great. :) – Trollwut Feb 18 '14 at 15:41
To retrieve data from the next `pord` you need to join to it. Not sure what more to explain? I added an alternative ... – Erwin Brandstetter Feb 18 '14 at 15:58
Well ok, I get that. :) But I don't get it why I didn't need it on my former question. https://stackoverflow.com/questions/21674589/recursive-query-in-postgresql But I'll just re-read it, as my head now is full of queries. :D Thanks again for your help! – Trollwut Feb 18 '14 at 16:10
@Trollwut: It's the same in your former question. The only difference is the additional table `pordnrzu`, where you store the link to the next node in this model. – Erwin Brandstetter Feb 18 '14 at 16:15

Recursive chain with JOIN element?

1 Answers1