How to Join nullable ID to not nullable ID in other table

[hugmarcel]

Hi

I have 2 tables, one contains field ID which can be nullable (have empty values). I need to join the table to another one containing field ID, where ID is never empty.

NULL value can join with any ID in table 2.

How can this be achieved?

Thx-  Marcel

[#2]

hey Marcel,

Question how can null be joint with any ID(is it totally random/ you mean it applies for all other valid IDs).

For example

ID1
1
2
3
-
-

ID2
1
2
3
4
5
6
7

Can you explain how you want it in the above case. Does 4th and 5th row in first table1 apply for all IDs in Table2  ?

Thanks

AJ

[hugmarcel]

yes

[MK_QSL]

If there is only one ID matching between two tables, it is impossible to get merged data.

You can either Load Nullable table with

Load * From TableName where NOT ISNULL(ID);

or

Load *, IF(ISNULL(DI), 'UNKNOWN',ID) as ID from TableName;

[hugmarcel]

I do not want to loose the empty IDs of table one.

"UNKNOWN" value does not help as table 2 does not contain value UNKNOWN.

Goal is just not to loose the empty IDs in table one when joining.

- Shall I add an artificial row having value UNKNOWN in table2?

- Or is there means with outer joining or something?

thx - Marcel

[MK_QSL]

Better to add one more line as UNKNOWN is another table...

[maxgro]

perhaps I don't understand but if you join 2 tables you don't lose nulls

T1:

load if(ID='-', null(), ID) as ID, 'T1-' & ID as COL1;

LOAD * INLINE [

ID

1

2

3

-

-

];

T2:

LOAD *, 'T2-' & ID as COL2;

LOAD * INLINE [

ID

1

2

3

4

5

6

7

];

DROP Field COL1, COL2;

Table:

NoConcatenate load * Resident T2;

join (Table) LOAD * Resident T1; 

DROP Table T1, T2;

[#8]

I think I understood what you are trying to do.Lets say:

Table1:                                                               

ID	Country
1	US
2	US
3	UK
4	UK
5	China
-	Mexico
-	Canada

Table2:

ID	Amount
1	23
2	45
3	78.8
4	56
5	33.3

Result:

Check out the attached file. Hope it helps.