I was able to get this to work using an InlineQuerier that has 2 output ports. The DMA port outputs all "matched" records, and the DBF1 port outputs all records from DBF1. I used the following SQL statement for the DMA port:
SELECT "DMA","Description","ID"
FROM "DBF1" JOIN "DBF2"
ON "Description" IN ("I1","I2","I3","I4","I5","I6","I7","I8")
Then I used a FeatureMerger (DBF1 = Requestor, DMA = Supplier) that joined on Description. I connected the Merged port AND the UnmergedRequestor port to an AttributeManager (this gives me 1 stream with my 12 original records) - In the AttributeManager, I created the field Inflow, and set it to DMA, if DMA has a value. Otherwise, Inflow is set to a space. Then I removed the DMA field.
This is the output I got:
Here is a screenshot of the entire workspace:
And a screenshot of the AttributeManager properties:
I have also attached the workspace, in case you want to look at it.
Originally, I was going to output the "non-merged" features through another port of the InLineQuerier, but I also was unable to get the NOT IN statement to work successfully. This process is a little clunkier, but it works.
-Courtney
This SQL statement could also be a possible way.
select
a.*,
case
when b.DMA is null then ''
else b.DMA
end as Inflow
from DBF1 as a
left outer join (select * from (
select DMA, l1 as Description from DBF2
union all select DMA, l2 from DBF2
union all select DMA, l3 from DBF2
union all select DMA, l4 from DBF2
union all select DMA, l5 from DBF2
union all select DMA, l6 from DBF2
union all select DMA, l7 from DBF2
union all select DMA, l8 from DBF2
) where Description <> '') as b
on b.Description = a.Description
Alternatively, this Non SQL based workflow could work as well.
