Skip to main content
  • EN

I have a list of connections (ServiceID) that are attached to a main (MainID), that are grouped by a network ID (NetworkID). There can be one or more connections to a single main, and there can be multiple mains in a network. See example -

BeforeI need to list out the mains with all possible service connections within a network. Each main ID would have all connections within that network

So the above example needs to turn into this -

AfterI thought I would be able to use a feature merger, or joiner, grouping by network ID, with both requester and supplier coming from the same table (kind of like a many-many join) but that doesn't seem to work. Maybe my parameters are off, but I am not even sure if my logic is on the right track. I am somewhat new to FME. Any suggestions would be welcome!!

You can use a CROSS JOIN, also referred to as a Table Cartesian Product.

Let us call the Data Table "A", and we want to create the Cartesian Product that is A.MainID x A.ServiceID x A.NetworkID ie. All combinations possible from the unique values of A.MainID, A.ServiceID, A.NetworkID

 

In the FME InlineQuerier Transformer, the SQL Statement to generate this is:

 

SELECT A1.MainID, A2.ServiceID, A3.NetworkID
FROM
(SELECT MainID FROM A GROUP BY MainID) AS A1
CROSS JOIN
(SELECT ServiceID FROM A GROUP BY ServiceID) AS A2
CROSS JOIN
(SELECT NetworkID FROM A GROUP BY NetworkID) AS A3

Or semantically the same, but with less wordiness: Instead of explicitly stating a CROSS JOIN, this can be implicitly written with just a comma separator character between A1, A2, A3:

 

SELECT A1.MainID, A2.ServiceID, A3.NetworkID
FROM
(SELECT MainID FROM A GROUP BY MainID) A1,
(SELECT ServiceID FROM A GROUP BY ServiceID) A2,
(SELECT NetworkID FROM A GROUP BY NetworkID) A3

The GROUP BY ensures that only the unique values are returned in Subqueries A1, A2, A3. The same result can be achieved by instead using Eg. (SELECT DISTINCT MainID FROM A) AS A1 , but DISTINCT generally executes slower than using the GROUP BY method.

Reply


Community Stats

30,185
Posts
114,572
Replies
37,848
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.