It's hard to think of a solution unless you specifically explain what rules are applied to determine if two building names from the two tables match.

Sorry I was not explained well.

I have two table and both are having "BldName" field.In second table "BldName" fields values contains in first table "BldName" field value based on that need to join the table to get Type for first table.

example : IdABC = ABC

Expected result is like shown below.

Not clear.

Every building name in the Table 1 seems to be equal to a string consisting of a building name in the Table 2 and a specific prefix or a specific suffix in the Table 2. Is it the rule?

If so, are only these three patterns possible? Or are there any other patterns?

• ld<building name>
• <building name>bl
• <building name>bld

There is no specific prefix and suffix.

Example 01:

First table :

Building name = "Beach 01 House"

Second table:

Building name = "Beach 01"

Example 01:

First table :

Building name = "Green 01 Home"

Second table:

Building name = "Green 01"

above two example there are common name is "Beach 01" and "Green 01"

based on this common string need to join two tables.

Two possible ways.

1. FeatureMerger: Merge attributes from every table1 feature to every table1 feature unconditionally as a list attribute.
2. ListSearcher: Find an element in the list that contains the BldName from table2.
3. ListIndexer: Extract the element (BldName and Construction Status from table1) as attributes.

Use the InlineQuerier with this SQL statement.

select
    a."BldName",
    a."Construction Status",
    b."Type"
from "table1" as a
inner join "table2" as b
on a."BldName" like '%'||b."BldName"||'%'

I see 2 potential options:

1) If the tables are not too big:

Prefix both table1 attrbutes and table 2 attributes (t1 and t2) using BulkAttributeRenamer

Use FeatureMerger (in 2018 FeatureJoiner) to merge unconditionally (1 = 1)

Use the Tester to find t1.BldName Contains y2.BldName.

This should get you the required result, but takes long if the tables grow.

2) Use the InlineQuerier transformer to build a query like:

Select t1.BldName, t1."Constructions status", t2.Type

From "table 1" t1, "table 2" t2

Where t1.BldName like '%'||t2.BldName||'%'

That too should get the same result.

Hope this helps.

I wonder if using an InlineQuerier could work, maybe using a join like this:

SELECT
 Table1.BldName,
 Table1.ConstructionStatus,
 Table2.Type
FROM Table1
JOIN Table2
ON Table1.BldName LIKE '%' + Table2.BldName + '%'

I can't test it right now for the exact syntax, but perhaps using this principle, it could work.

Can confirm option 2 works. Learned something new today!

Would prefer the use of the InlineQuerier over prefixing and featuremerging both tables due to performance.

What I miss are the FeatureMergers UnmergedRequestor and UnuserSupplier ports for total control. Is this doable with SQL?

inlinequerier.fmwt