Joining more than two tables in a Flowscript query

Flow Connect
1

I have multiple results being returned from HTTP calls. I have each of them and have them returning to a distinct results variable, but when I try to join them together in a script step, I can only do one join at a time. I can get around it like below where I just join the first two to a temp table, then call that table and join to the 3rd and so on

let tmp1 = SELECT c.CustomerId
, c.Name
, ca.Address1
, ca.Address2
, ca.City
, ca.State
, ca.ZipCode

FROM CustomerListResult.Results as c       
JOIN CustomerAddressResult.Results as ca ON c.CustomerId = ca.CustomerId;

let tmp2 = SELECT t1.CustomerId                
, t1.Name                
, t1.Address1                
, t1.Address2                
, t1.City                
, t1.State                
, t1.ZipCode                
, coca.RegionCode                
, coca.DistrictCode

FROM tmp1 as t1           
JOIN CustomerOrderAddressResult.Results as coca ON t1.CustomerId = coca.CustomerId

But if possible I’d prefer to just do it in one SQL statement. I’m trying to do it this way rather than creating a custom projection for it.

If I try to join more than two tables, on the 3rd one I’ll get an error

FROM CustomerListResult.Results as c
JOIN CustomerAddressResult.Results as ca ON c.CustomerId = ca.CustomerId
JOIN CustomerOrderAddressResult.Results as coca ON t1.CustomerId = coca.CustomerId;

gives me this

Expected a ‘(seq: primitive*, separator: text’, but the term has type ‘{ (then it gives me my table definition)}*’

Any suggestions to joining more than two tables at a time, or is the tmp table option the best route?

2

Hi,

With the ‘join’ clause you can join only 2 tables at the same time.
”Join clauses are used to combine records from two sequences based one some relationship between the records.” ( Queries | Flow Connect Help )

You can combine those joins into one statement like this (but it is logically the same as the temp table solution):

let tmp4 = SELECT tmp1.CustomerId, 
                  tmp1.Name,
                  tmp1.Address1,
                  tmp1.Address2,
                  tmp1.City,
                  tmp1.State,
                  tmp1.ZipCode,
                  coca.RegionCode, 
                  coca.DistrictCode 
            FROM (SELECT c.CustomerId
                        , c.Name
                        , ca.Address1
                        , ca.Address2
                        , ca.City
                        , ca.State
                        , ca.ZipCode        
                    FROM CustomerListResult.Results as c JOIN CustomerAddressResult.Results as ca ON c.CustomerId = ca.CustomerId ) as tmp1 
                                                         JOIN CustomerOrderAddressResult.Results as coca ON tmp1.CustomerId = coca.CustomerId;

Best,
Zoltan

2 Likes