CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressionrecursive-query
Joe
Joe

Reputation: 1388

PostgreSQL Recursive CTE issue with JOINs inside

I have a PostgreSQL DB with two tables that I need to use to do a recursive query.
The two tables look like this:

Main table
Box         pipeline    plate   solution
----------------------------------------
X000001     Pipe        10000   75750
X000001     Pipe        10000   75751
X000001     Pipe        10001   75752
X000001     Line        20000   75750
Y000002     Pipe        10007   75800
...


Mixture (Solution History)
made_solution_id     used_solution_id
-------------------------------------
75750                66746
75750                73002
66746                76380
66746                80000
...

The main table as you can see can have multiple solutions per plate, multiple plates per pipeline, and multiple pipelines per box. The Mixture table is made such that several solutions are used to make 1 solution (eg 66746 and 73002 were used to make 75750).

What I need to do is FOR EVERY ROW do a recursive query on the Mixture table so that the entire history all the way up to the start is returned.

eg for the first row from the main table:

Box     pipeline    plate   made_solution_id     used_solution_id
-----------------------------------------------------------------
X000001 Pipe        10000   75750                66746
X000001 Pipe        10000   75750                73002
X000001 Pipe        10000   66746                76380
X000001 Pipe        10000   66746                80000

(all the way recursively until there are no parent solutions used to make the solution) ... do this for all rows in the main table

So for doing that, I came up with this recursive query:

WITH RECURSIVE parents (box, pipeline, plate, made_solution_id, used_solution_id)
AS (
    --get leaf solution
    SELECT
    box, pipeline, plate, made_solution_id, used_solution_id
    FROM
    main
    JOIN
    mixture
    ON
    solution = made_solution_id

UNION
    --get parent solutions
    SELECT
    t.box, t.pipeline, t.plate, m.made_solution_id, m.used_solution_id
    FROM
    main t
    JOIN
    parents pt
    ON
    pt.made_solution_id = t.solution
    JOIN
    mixture m
    ON
    pt.used_solution_id = m.made_solution_id
)
SELECT * from parents

This, however, did not work. It seems like my recursive step isn't working -- the query DOES go through all the rows in the Main table, but it just returns the results of what would be a JOIN between Main table and Mixture table.

So instead of looking like the example results above, it looks like this instead:

Box     pipeline    plate   made_solution_id     used_solution_id
-----------------------------------------------------------------
X000001 Pipe        10000   75750                66746
X000001 Pipe        10000   75750                73002

What am I doing wrong? I read the recursive query documents and also looked at several questions on here for recursive CTEs, but I'm stuck.

EDIT: Changed something in SQL Query and fiddle provided by Erwin Brandstetter http://www.sqlfiddle.com/#!11/9354f/4

PostgreSQL version 8.4.17 by the way.

EDIT2: This is a portion of what my query returns on psql (NOTE: This is just one example where the recursive query just stops doing to recursive thing after a couple rows):

 XYZ01   | High | 114043 |               49923 |               46573
 XYZ01   | High | 115424 |               49923 |               46573
 XYZ01   | High | 114043 |               46573 |               39853
 XYZ01   | High | 115424 |               46573 |               39853
 XYZ01   | High | 114043 |               46573 |               20456
 XYZ01   | High | 115424 |               46573 |               20456
 XYZ01   | High | 116694 |               49923 |               46573
 XYZ01   | High | 116691 |               49923 |               46573
 XYZ01   | High | 116697 |               49923 |               46573
 XYZ01   | High | 116693 |               49923 |               46573
 XYZ01   | High | 116696 |               49923 |               46573
 XYZ01   | High | 116699 |               49923 |               46573
 XYZ01   | High | 116698 |               49923 |               46573
 XYZ01   | High | 116692 |               49923 |               46573
 XYZ01   | High | 116695 |               49923 |               46573

Which... isn't what the fiddle returns.

Upvotes: 0

Views: 927

Answers (1)

user3491224
user3491224

Reputation:

You need to return the box, pipeline, plate of m, not t, in your second part of CTE. Otherwise you're going to go through solutions, yes, but not going to display what you're currently on for the rest of the stuff (which is what seems like you want). You'll instead display the first one only.

WITH RECURSIVE parents (box, pipeline, plate, made_solution_id, used_solution_id)
AS (
    --get leaf solution
    SELECT
    box, pipeline, plate, made_solution_id, used_solution_id
    FROM
    main
    JOIN
    mixture
    ON
    solution = made_solution_id

UNION
    --get parent solutions
    SELECT
    m.box, m.pipeline, m.plate, m.made_solution_id, m.used_solution_id
    FROM
    main t
    JOIN
    parents pt
    ON
    pt.made_solution_id = t.solution
    JOIN
    mixture m
    ON
    pt.used_solution_id = m.made_solution_id
)
SELECT * from parents

Upvotes: 2

Related Questions