Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have three tables
TableA:
Customer ID
TableB:
Customer ID
Indicator
TableC:
Customer ID
Indicator
I am doing a Left Join on TableA with TableB to join 'Indicator' Field. Not every Customer ID has Indicator values in TableB and which is why I want to do another left join over TableC, which contains those missing 'Indicator' values. Since the field names are same (Indicator), the 2nd left join fails to add the missing values.
How do I achieve this, with 'Indicator' values from TableB and TableC in a single field?
I think the suggestion of concatenating at first the joining tables goes in the right direction but this concatenating should be done with a DISTINCT statement because otherwise there may be duplicates in the key-values which would increase the number of records.
Probably better would be to use mappings instead of a join approach. Mappings have no risk to change the number of records and are more flexible in many ways, for example to define an order from which table the match-value should come from at first and/or to set a default-value for non-matching keys. This may look like this:
mapB: mapping load * from TableB;
mapC: mapping load * from TableC;
TableA: load *, applymap('mapB', [Customer ID], applymap('mapC', [Customer ID], 'myDefault')) as Indicator
from TableA;
- Marcus
There's probably a shorter form for this, but off the top of my head:
Table A:
Load Customer ID
From TableA;
TableTemp:
Load
Customer ID
Indicator
From TableB;
CONCATENATE
Load
Customer ID
Indicator
From Table C;
Left Join(TableA)
Load
Customer ID
Indicator
Resident TableTemp;
Drop Table TableTemp;
Yes, this code should work. Table B & C should be concatenated before joining with A.
I think the suggestion of concatenating at first the joining tables goes in the right direction but this concatenating should be done with a DISTINCT statement because otherwise there may be duplicates in the key-values which would increase the number of records.
Probably better would be to use mappings instead of a join approach. Mappings have no risk to change the number of records and are more flexible in many ways, for example to define an order from which table the match-value should come from at first and/or to set a default-value for non-matching keys. This may look like this:
mapB: mapping load * from TableB;
mapC: mapping load * from TableC;
TableA: load *, applymap('mapB', [Customer ID], applymap('mapC', [Customer ID], 'myDefault')) as Indicator
from TableA;
- Marcus
first create a table D which is table B and a concat of table C. Drop table B and C, and do the left join of table D on table A
When our data was all in a single table, we could easily retrieve a particular paygonline row from that table in order to get all the pertinent data we needed for a particular query. For example, looking at our unnormalized table below, if we wanted information on the books that 'John Smith' has checked out.
Thank you all. This is really helpful