What is the best practice for tMap if multiple parameters point to id of same lookup table?

[jvesely]

Hi,

I have a legacy datamodel that I cannot change (and didn't design aka "it wasn't me" 😉 ) . There's many tables but this question is about following situation:

Table 1 (source)

• id
• fk1 to table 2
• fk2 to table 2
• fk3 to table 2

Table 2 (lookup)

• id

Further, if fk1 has a value, fk2 and fk3 are null. If fk2 has a value, fk1 and dfk3 are null etc.

I need to delete records from table 1 that do not point to any valid record in table 2.

I can think of 2 approaches to do this:

1) Use table 2 in 3 lookups, meaning the tMap will have Table 1 as input and 3 (copy & paste) lookups op Table 2

2) Actually twist the situation: Use table 2 as source table and table 1 as lookup

I'd hate to use method 1 as I hate copy & paste. Method 2 seems like it could work (need to test this though).

But is there an even better approach for situations like this?

Thanks!

If my question isn't clear, let me know and I'll try to write it in a different way / add a screenshot

[Anonymous · Accepted solution]

So only 1 of the FK column is populated from the 3?

This means if you'd do a coalesce(fk1,fk2,fk3) as fk you could simply join Table 2. (There's no coalesce in java but it can be easily done.)

( fk1 != null ? fk1 : (fk2 != null ? fk2 : fk3) ) -> table2.id

Then you want to do a LEFT Join inside tMap, (by default it's kind of INNER join) and then filter for the lookup being null.

[jvesely]

Of course, makes sense. Thank you!