Left join of without row expansion

[#1]

Hi guys,

I have a problem I have encountered a few times already. I would like to make a left join and at the same time be sure I don't expand the number of rows of the base table. The purpose is to get one fieldA for each join_key in the BaseTable, it does not matter whether it is AA1, AA2 or AA3.

BaseTable
join_key	field1	field3
abc	john	williams
def	paul	jones
ghi	david	terry

JoinTable
join_key	fieldA
abc	AA1
abc	AA2
abc	AA3

So what I would like to do the following join and make sure I will only get one value for fieldA for each join_key.

LEFT JOIN( BaseTable) LOAD * Resident JoinTable;

DROP TABLE JoinTable;

My current approach is to make an aggregation of the JoinTable in order to ensure the join_key is unique. With numbers, I use a min() or max() function. With text fields, I use min(autonumber(join_key & fieldA)) as an aggregation formula.

I think I am making it more complicated than is necessary, so do you know a better way?

[hic]

The demand that it should “not expand the number of rows” implies that you cannot have a one-to-many relationship. You need to find a way to collapse several records into one, just like flipside indicates above, using Concat. But you write that “it does not matter whether it is AA1, AA2 or AA3”. So you could use the first found value and skip the others.

Then the ApplyMap solution will also work. It will always use the first value found.

[flipside]

Hi Henric,

Yes, if it was me I would be trying as many options as I could think of and use the one with the best performance.

My suggestion could also be improved by changing to ...

Left Join (BaseTable)

load join_key, FirstValue(fieldA) as vals resident JoinTable group by join_key;

flipside

[#4]

Thanks everybody so far.

@Henric, the applymap solution makes sense to me. I will test this and let you know if it has worked out.

@Flipside: I didn't know the Firstvalue() aggregation! I was actually looking for something like this, because it is faster than a min() or max() function and it also suits text fields.