From 29b0e2b5d0e26326f2a4d611553c9e49e1544676 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 24 Oct 2025 07:48:00 +0000 Subject: [PATCH] Fix lateral joins --- conversion/clean.sql | 17 ++++++++++++----- 1 file changed, 12 insertions(+), 5 deletions(-) diff --git a/conversion/clean.sql b/conversion/clean.sql index d4bc793..2560b9d 100644 --- a/conversion/clean.sql +++ b/conversion/clean.sql @@ -394,20 +394,27 @@ UPDATE follow_arrival JOIN follow ON (follow.fol_date = spans.fa_fol_date AND follow.fol_b_animid = spans.fa_fol_b_focal_animid) - , LATERAL ( -- Stick a min_nums.min_seq column on the end of each row - SELECT min_seqs.min_seq + JOIN LATERAL ( -- Stick a min_nums.min_seq column on the end of each row + SELECT min_seqs.fa_fol_date + , min_seqs.fa_fol_b_focal_animid + , min_seqs.min_seq FROM min_seqs WHERE min_seqs.fa_fol_date = spans.fa_fol_date AND min_seqs.fa_fol_b_focal_animid = spans.fa_fol_b_focal_animid - AND min_seqs.fa_b_arr_animid = spans.fa_b_arr_animid ) AS min_nums - , LATERAL ( -- Stick a max_nums.max_seq column on the end of each row - SELECT max_seqs.max_seq + ON (min_nums.fa_fol_date = spans.fa_fol_date + AND min_nums.fa_fol_b_focal_animid = spans.fa_fol_b_focal_animid) + JOIN LATERAL ( -- Stick a max_nums.max_seq column on the end of each row + SELECT max_seqs.fa_fol_date + , max_seqs.fa_fol_b_focal_animid + , max_seqs.max_seq FROM max_seqs WHERE max_seqs.fa_fol_date = spans.fa_fol_date AND max_seqs.fa_fol_b_focal_animid = spans.fa_fol_b_focal_animid AND max_seqs.fa_b_arr_animid = spans.fa_b_arr_animid ) AS max_nums + ON (max_nums.fa_fol_date = spans.fa_fol_date + AND max_nums.fa_fol_b_focal_animid = spans.fa_fol_b_focal_animid) WHERE follow_arrival.fa_fol_date = spans.fa_fol_date AND follow_arrival.fa_fol_b_focal_animid = spans.fa_fol_b_focal_animid AND follow_arrival.fa_b_arr_animid = spans.fa_fol_b_focal_animid -- 2.34.1